How to show data using datagrid with C# + SQL Server

Question

I want to ask more to show data from SQL Server to WinForm using a datagrid. I've been creating a datagrid and the stored procedure to show data is

ALTER PROC [dbo].[SP_GetData]
AS
  SELECT nama , nim
  FROM tabledata

and I've created the function to access the database and the stored procedure in C#

string Sp_Name = "dbo.SP_GetData";

SqlConnection SqlCon = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBMahasiswa;Data Source=.");
SqlCon.Open();

SqlCommand SqlCom = new SqlCommand(Sp_Name , SqlCon);
SqlCom.CommandType = CommandType.StoredProcedure;

List<mahasiswaData> listMahasiswa = new List<mahasiswaData>();

using (SqlDataReader sqlDataReader = SqlCom.ExecuteReader())
{
   if (sqlDataReader.HasRows)
   {
      while (sqlDataReader.Read())
      {
         mahasiswaData DataMhs = new mahasiswaData();
         DataMhs.Nama = sqlDataReader["Name"].ToString();
         DataMhs.Umur = Convert.ToInt32(sqlDataReader["Age"]);
         listMahasiswa.Add(DataMhs);
      }
   }
}

SqlCon.Close();
return listMahasiswa;

and finally, in the show button I add this code

dgvmahasiswa.DataSource = new MahasiswaDB().LoadMahasiswa();

Could somebody tell me where the fault is or the alternatives one?

Thank You So Much! :D

What exactly is wrong with it? Apart from suggesting some refactoring, not returning null in the catch block (you should really be logging errors at the very least so you know if things are going wrong), and more use of the using keyword, this looks like it would work. Also note that if your query runs but returns no rows, you'll just return an empty listMahasiswa — dash
– dash, Commented Apr 22, 2012 at 14:49
Really big thanks for the comment :) :) I have added try - catch in case preventing some error.. But still , I always receive false return in the end.. FYI , I've added some data to the table in database.. — pegasustech
– pegasustech, Commented Apr 23, 2012 at 4:20

dash · Accepted Answer · 2012-04-22 17:04:13Z

2

Some things to think about:

At the moment, if your code runs into exceptions, you'll leave a SqlConnection hanging around; you've used the using pattern for your SqlDataReader; you should extend it to all of your disposable objects.
You are swallowing exceptions; if your query fails, the connection cannot be made, or something else happens, you'll never really know - your function will just return null.
Is it possible for name or age to be null? Age to be non-numeric? There's no test for any unexpected values, which you'll also never know about.
If you don't have any records, you'll return an empty list. Is this desired? Or would you prefer to know there were no records?

You might prefer to look at something like this:

public List<mahasiswaData> GetData(){


    List<mahasiswaData> gridData = new List<mahasiswaData>();


    try{

        using(SqlConnection conn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBMahasiswa;Data Source=."))
        {
            using(SqlCommand command = new SqlCommand())
            {
                command.Connection = conn;
                command.CommandType = CommandType.StoredProcedure;
                command.Text = "dbo.SP_GetData";

                using(SqlDataReader reader = command.ExecuteReader())
                {
                    if(reader.HasRows){
                        while(reader.Read())
                        {
                           object rawName = reader.GetValue(reader.GetOrdinal("Name"));
                           object rawAge = reader.GetValue(reader.GetOrdinal("Age"));

                           if(rawName == DBNull.Value || rawAge == DBNull.Value)
                           {
                               //Use logging to indicate name or age is null and continue onto the next record
                               continue;
                           }
                           //Use the object intializer syntax to create a mahasiswaData object inline for simplicity
                           gridData.Add(new mahasiswaData()
                                                   {
                                Nama = Convert.ToString(rawName),
                                                        Umur = Convert.ToInt32(rawAge) 
                                                   });


                        }
                    }
                    else{
                        //Use logging or similar to record that there are no rows. You may also want to raise an exception if this is important.
                    }

                }

            }


        }


    }
    catch(Exception e)
    {
       //Use your favourite logging implementation here to record the error. Many projects use log4Net
       throw; //Throw the error - display and explain to the end user or caller that something has gone wrong!
    }


    return gridData;


}

Note that if you are sure that age or name will never be null then you can simplify the middle section:

while (reader.Read())
{
    //Use the object intializer syntax to create a mahasiswaData object inline for simplicity
    gridData.Add(new mahasiswaData()
    {
        Nama = reader.GetString(reader.GetOrdinal("Name")),
        Umur = reader.GetInt32(reader.GetOrdinal("Age"))
    });

}

edited Apr 22, 2012 at 17:04

answered Apr 22, 2012 at 15:27

dash

91.8k4 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pegasustech Over a year ago

Big Appreciate :D :D I want to try your code , but let me learn your code for a moment.. :) Could I ask something more , I do not edit everything in the datagrid properties.. Just add some code behind them.. Is there anything wrong with it?

dash Over a year ago

There shouldn't be @pegasustech. It should work, but it might not look as good as it could do with all of the necessary properties set. It's worth just getting data in first, then playing with the presentation of the grid.

pegasustech Over a year ago

Big Thanks @dash It seems you are the one who answers my question lately :D :D The problem is solved now , The main problem is I point to another database at my sql server.. big thanks :)

Collectives™ on Stack Overflow

How to show data using datagrid with C# + SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related