Insert & Retrieve from Service Based Database

Now, we go a bit deeper in the SQL database in C# as we learn how to Insert as well as Retrieve a record from a Database. Start off by creating a Service Based Database which accompanies the default created form named form1. Click Next until finished. A Dataset should be automatically created. Next double click on the Database1.mdf file in the solution explorer (Ctrl + Alt + L) and carefully select the connection string. Format it in the way as shown below by adding the @ sign and removing a couple of "=" signs in between. The connection string in .Net Framework 4.0 does not need the removal of @ & "=" signs. The String is generated perfectly ready to use. This step only applies to .Net Framework 1.0 & 2.0.


There are two things left to be done now. One to insert & then to Retrieve. We create an SQL command in the standard SQL Query format. Therefore, inserting is done by the SQL command: 
Insert Into <TableName> (Col1, Col2....) Values (Value for Col1, Value for Col2....). 

 Execution of the Command/SQL Query is done by calling the function ExecuteNonQuery(). The execution of a command Triggers the SQL query on the outside and makes permanent changes to the Database. Make sure your connection to the database is open before you execute the query and also that you close the connection after your query finishes.

To Retrieve the data from Table1 , we insert the present values of the table into a DataTable from which we can retrieve & use in our program easily. This is done with the help of a DataAdapter. We fill our temporary DataTable with the help of the Fill(TableName) function of the DataAdapter which fills a DataTable with values corresponding to the select command provided to it. The select command used here to retreive all the data from the table is: 
Select * From <TableName> 

 To retreive specific columns use: 
Select Column1, Column2 From <TableName>.

 Hints:
  1. The Numbering of Rows Starts from an Index Value of 0 (Zero)
  2. The Numbering of Columns also starts from an Index Value of 0 (Zero)
  3. To learn how to Filter the Column Values Please Read Here
  4. When working with SQL Databases use the System.Data.SqlClient  namespace
  5. Try to create and work with low number of DataTables by simply creating them common for all functions on a form. 
  6. Try NOT to create a DataTable every-time you are working on a new function on the same form because then you will have to carefully dispose it off.
  7. Try to generate the Connection String dynamically by using the Application.StartupPath.ToString() function so that when the Database is situated on another computer the path referred is correct. 
  8. You can also give a folder or file select dialog box for the user to choose the exact location of the Database which would prove flawless.
  9. Try instilling Datatype constraints when creating your Database. You can also implement constraints on your forms(like NOT allowing user to enter alphabets in a number field) but this method would provide a double check & would prove flawless to the integrity of the Database.

using System;
using System.Data.SqlClient;

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

        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename="+
        + Application.StartupPath.ToString() + "\\Database1.mdf;Integrated Security=True;
        User Instance=True");

        private void Form1_Load(object sender, EventArgs e)
        {
            MessageBox.Show("Click on Insert Button & then on Show");
        }

        private void btnInsert_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO Table1 (fld1, fld2, fld3) VALUES (" 
            + "'I '" + "," + "' LOVE '" + "," + "' code-kings.blogspot.com'" + ")", con);
 
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        private void btnShow_Click(object sender, EventArgs e)
        {
            DataTable table = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter("Select * from Table1", con);
            con.Open();
            adp.Fill(table);
            MessageBox.Show(table.Rows[0][0] + " " + table.Rows[0][1] + " " + 
            table.Rows[0][2]);
        }
    }
}


Please Note :
** Do not Copy & Paste code written here ; instead type it in your Development Environment
** Testing done in .Net Framework 4.5 but code should be very similar for previous versions of .Net
** All Program Codes written here are 100%  tested & running
Labels: , , , , , , , ,