dataGridView + architectural tips for beginner

[Krystian81]

Hi everybody,
first of all I`s like to point that I`m a C# beginner and I would like to learn from scrach using good practices. What I need to do is:
1. Query databese
2. Return results in dataGridView ( 1st or last column should contain a checkbox for further use ). Of course I can do this in one class ( like almost all tutorials ) but I would like to learn how to split and manage everything in this enviroment

So, what I`ve done so far:
1. I`ve added 3 folders: Access, Settings, Tools
2. In Tools
namespace Tools
{
class connectDB
{
protected SqlDataReader sqlread;
protected SqlDataAdapter sqladapter;
protected DataSet ds;
protected SqlConnection connection;
protected SqlCommand command;
}
}

3. In Settings
namespace Settings
{
class vars
{
public static string connect_string = "Server=ip;Database=myDbName;User Id=myUser;Password=myPass";
}
}

4. In Access

class accessDocument : Tools.connectDB
{

public accessDocument()
{
connection = new SqlConnection(Settings.vars.connect_string);
}

public void getWzList(int month, int year)
{
connection.Open();
command = connection.CreateCommand();

command.Connection = connection;

try
{
command.CommandText = selectQuery(month, year);
sqlread = command.ExecuteReader();

}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "UWAGA");
}
finally
{
if (connection != null)
{
connection.Close();
}
}

}

private string selectQuery(int month, int year)
{
string sqlStatement = "";

sqlStatement = "SELECT * " +
"FROM cdn.testTable " +
"WHERE Typ = 2001 and rok = " + year + " and MONTH(dbo.toDate(dataWyst, 0)) = " + month;

return sqlStatement;
}

}

and finally I`m trying to use it like this

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
Access.accessDocument doc = new Access.accessDocument();
doc.getWzList(1, 2012);
// and I don`t know what to do, of course grid is empty
}
}

 

[ralphtrent]

first you need to have getWzList return your data

public DataTable getWzList(int month, int year)

Then when you execute your query you need to store the data somewhere to return it

DataTable ldtResults = new DataTable();
SqlDataAdapter lSqlAdap = new SqlDataAdapter();
try
{
     lSqlAdap.SelectCommand = command;
     command.CommandText = selectQuery(month, year);
     lSqlAdap.Fill(ldtResults );
}
catch{...}
finally{... do your clean up of connection and objects ...}
return ldtResults;

Now bind your grid to the data

private void button1_Click(object sender, EventArgs e)
{
      Access.accessDocument doc = new Access.accessDocument();
      DataTable ldtResults = doc.getWzList(1, 2012);
     DataGridControlID.DataSource = ldtResults ;
     DataGridControlID.DataBind();
}

Give this a try

 

[Krystian81]

Hi ralphtrent,
thank you for your answer. It was very useful,

I change my code concerning your tips trying to add checkboxex to each row -> succesfully.
Now I would like to know how to get the ID ( or ID`s ) of marked row ( id is returned from the query ).

       public DataTable getWzList(int month, int year)
        {
            connection.Open();

            command = connection.CreateCommand();
            command.Connection = connection;

            try
            {
                adapter = new SqlDataAdapter(selectWzListQuery(month, year), connection);

                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                adapter.Fill(table);

                return table;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "UWAGA");
                return null;
            }
            finally
            {
                if (sqlread != null)
                    sqlread.Close();
                
                if (connection != null)
                    connection.Close();
            }

        }

            Access.accessDocument doc = new Access.accessDocument();
            
            dbGridView.DataSource = doc.getWzList((int)this.p_month.Value, (int)this.p_year.Value);

            DataGridViewCheckBoxColumn checkBox = new DataGridViewCheckBoxColumn();
            checkBox.HeaderText = "Zaznacz/Odznacz";
            checkBox.Name = "chkBox";
            checkBox.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dbGridView.Columns.Add(checkBox);