dataGridView + architectural tips for beginner

Krystian81 · Oct 10, 2012

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 · Oct 24, 2012

first you need to have getWzList return your data

Code:

public DataTable getWzList(int month, int year)

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

Code:

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

Code:

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 · Oct 29, 2012

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 ).

Code:

       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();
            }

        }

Code:

            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);

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

dataGridView + architectural tips for beginner

Krystian81

Programmer

ralphtrent

Programmer

Krystian81

Programmer

Similar threads

Part and Inventory Search

Sponsor