How To Insert Row In Sql Database In Ado.net Connection Oriented Mode

I have a database in which a table has name Registration which is used to register the user. It has only two column one is Username and one is password. A page named Register.aspx

Solution 1:

ADO.NET has DataReader which supports Connected mode. All else are disconnected.

DataReader is connected architecture since it keeps conneection open untill all records are fetched

If you want to insert in ADO.NET then you should perform the following steps:

privatevoidbtnadd_Click(object sender, EventArgs e)
{
  try
  {
   //create  object  of Connection Class..................
   SqlConnection con = new SqlConnection();

   // Set Connection String property of Connection object..................
  con.ConnectionString = "Data Source=KUSH-PC;Initial Catalog=test;Integrated           Security=True";

 // Open Connection..................
  con.Open();

 //Create object of Command Class................
 SqlCommand cmd = new SqlCommand();

//set Connection Property  of  Command object.............
cmd.Connection = con;
//Set Command type of command object//1.StoredProcedure//2.TableDirect//3.Text   (By Default)

cmd.CommandType = CommandType.Text;

//Set Command text Property of command object.........

cmd.CommandText = "Insert into Registration (Username, password) values ('@user','@pass')";

//Assign values as `parameter`. It avoids `SQL Injection`
cmd.Parameters.AddWithValue("user", TextBox1.text);
cmd.Parameters.AddWithValue("pass", TextBox2.text);

 Execute command by calling following method................
  1.ExecuteNonQuery()
       This is used for insert,delete,update command...........
  2.ExecuteScalar()
       This returns a single value .........(used only forselect command)
  3.ExecuteReader()
     Return one or more than one record.

  cmd.ExecuteNonQuery();
  con.Close();


  MessageBox.Show("Data Saved");          
  }
     catch (Exception ex)
     {
            MessageBox.Show(ex.Message);
            con.Close();
     }


    }

Solution 2:

try
        {

            using (var connection = new SqlConnection(yourConnectionString))
            {
                string queryString = "Select id, name, age from Table where name = @name";
                using (var command = new SqlCommand(queryString, Connection))
                {
                    command.Parameters.AddWithValue("@name", name);

                    Connection.Open();
                    SqlDataReader dataReader = command.ExecuteReader();

                    while (dataReader.Read())
                    {
                        item = new Item(long.Parse(dataReader[0].ToString()),
                                        dataReader[1].ToString(),
                                        int.Parse(dataReader[2].ToString()));
                    }
                    dataReader.Close();
                }

            }
        }
        catch (Exception ex)
        {
           // if exception will happen in constructor of SqlConnection or Command, the // resource can leak but Dispose method will never be called, couse the object was not created yet.        // Trace and handle herethrow;
        }
        finally
        {

        }

But ADO.net is useless for enterprice development. You have to have and abstraction from Data Acess Layer and go to entities, not table records Use the ORM, Luke!

Solution 3:

using System.Data.SqlClient;

stringcnstr="server=.;database=dbname;user=username;password=password;";
SqlConnectioncon=newSqlConnection(cnstr);
SqlCommandcmd=newSqlCommand { Connection = con };
cmd.CommandText = "Insert into Registration (Username, password) values ('@user','@pass')";
cmd.Parameters.AddWithValue("user", TextBox1.text);
cmd.Parameters.AddWithValue("pass", TextBox2.text);
try
{
    con.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception)
{
    //something;
}
finally
{
    if (con != null)
       con.Close();
}