Eilz Eilz - 5 months ago 29
SQL Question

C# Need to put data from sql into DataGridView and sort by date at rows

First, I need to say sorry because I'm not good in English and I'm newbie of C#.
I have a problem about how to get only "Summ" values from my database And take it to row and sort by "mm" and "yy" thank for help.

Database:

enter image description here

I have a problem about how to get only "Summ" values from my database And take it to row and sort by "mm" and "yy" thank for help. And this is my design view (can't put Images) I just need to do it :D

enter image description here

CODE:

DateTime endtime = new DateTime();

DateTime starttime = new DateTime();

endtime = dateTimePicker2.Value;
starttime = dateTimePicker1.Value;
int mount = (((endtime.Year - starttime.Year) * 12) + (endtime.Month + 1) - starttime.Month);

textBox1.Text = mount.ToString();

string mm,yy;
string inp;
inp = textBox1.Text;
int dt;
dt = dataGridView1.Columns.Count;


int.TryParse(inp, out dt);
dataGridView1.Columns.Clear();
//sql connect

SqlConnection coco = new SqlConnection("Data Source=192.168.9.11;Initial Catalog=db;Persist Security Info=True;User ID=sa;Password=;");
coco.Open();
SqlCommand comm = new SqlCommand("SELECT Summ FROM test_recv Where mm like"+mm+"yy like "+yy+"");
try
{
coco.Open();
SqlDataReader reader = comm.ExecuteReader();

while (reader.Read())
{
string Summ = reader.GetString(0);
}

reader.Close();
coco.Close();
}
catch (Exception)
{
if (coco.State == ConnectionState.Open)
coco.Close();
}


for (int x = 0; x < dt; x++)
{
mm = starttime.AddMonths(x).ToString("MM", seCultureInfo);
yy = starttime.AddMonths(x).ToString("yyyy", seCultureInfo);
dataGridView1.Columns.Add("A", starttime.AddMonths(x).ToString("MM", seCultureInfo) + "/" + starttime.AddMonths(x).ToString("yyyy", seCultureInfo));
dataGridView1.Rows[0].Cells[x].Value = Summ;


}

Answer

Use this code where you want get your data and put the result in grid columns instead of rows:

var connection = new System.Data.SqlClient.SqlConnection(@"Your Connection String");

//Your command: SELECT yy+mm AS yymm, Summ FROM test_recv /*WHERE...*/ ORDER BY yymm ASC
//Add Whatever WHERE clause you need
//Pay attention that yy+mm Selected at first and Summ selected at seccond position
//Pay attention we ORDER BY yymm ASC
var command = new System.Data.SqlClient.SqlCommand("Your Command", connection);

try
{
    this.dataGridView1.Columns.Clear();
    this.dataGridView1.Rows.Clear();
    connection.Open();
    var reader = command.ExecuteReader();
    int columnIndex = 0;
    while (reader.Read())
    {
        //0 is Index of Summ
        var header = reader.GetString(0);

        //1 is Index of yymm
        var value= reader.GetString(1);

        var column = new DataGridViewTextBoxColumn();
        column.HeaderText = header;
        this.dataGridView1.Columns.Add(column);
        if(dataGridView1.RowCount==0)
            this.dataGridView1.Rows.Add(1);

        this.dataGridView1.Rows[0].Cells[columnIndex].Value = value;

        columnIndex++;
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    if (connection.State == ConnectionState.Open)
        connection.Close();
}

Here is test data

Summ    mm      yy      
------------------------
10000   08      2015
15000   09      2015      
8000    10      2015
20000   11      2015
5000    12      2015

and here is the screenshot:

enter image description here