Read scalar value from database

// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");
connection.Open();

// create and execute sql command
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT count(*) FROM MY_SCHEMA.Persons";
command.Connection = connection;
int i = (int)command.ExecuteScalar();

connection.Close();


More info and samples on: www.devarchweb.net

Populate datatable from database

// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");

// create SQLCommand
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM MY_SCHEMA.Persons";
command.Connection = connection;

// create DataTable with SqlAdapter and load data
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
try {
  connection.Open();
  int i = da.Fill(dt);
}
finally { connection.Close(); }


More info and samples on: www.devarchweb.net

Populate datable with SqlDataReader

DataTable dt = new DataTable();
using (SqlConnection connnection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True"))
{
  SqlCommand command = new SqlCommand("SELECT * FROM MY_SCHEMA.Persons", connnection);
  connnection.Open();
  SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
  if (dr.HasRows)
  {
    dt.Load(dr);
  }
  return dt;
}


More info and samples on: www.devarchweb.net

Read table data with SqlDataReader field by field

SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");

SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM MY_SCHEMA.Persons";
command.Connection = connection;

DataTable dt = new DataTable();

try
{
  connection.Open();

  SqlDataReader reader = command.ExecuteReader();
  bool tableCreated = false;
  while (reader.Read())
  {
    if (!columnsCreated)
    {
      // create table columns
      for (int i = 0; i < reader.FieldCount; i++)
      {
        string lsName = reader.GetName(i);
        Type t = reader[i].GetType();

        DataColumn column;
        if (t.Name != "DBNull")
        {
          column = new DataColumn(lsName, t);
        }
        else
        {
          column = new DataColumn(lsName, System.Type.GetType("System.Byte[]"));
        }

        dt.Columns.Add(column);
      }
      columnsCreated = true;
    }

    DataRow dr = dt.NewRow();
    for (int i = 0; i < reader.FieldCount; i++)
    {
      dr[i] = reader.GetValue(i);
    }
    dt.Rows.Add(dr);
  }
}
finally { connection.Close(); }


More info and samples on: www.devarchweb.net

Update data in the database with SQL

// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");
connection.Open();

// create and execute sql command
SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE MY_SCHEMA.Persons SET Height = 185 WHERE firstName='John'";
command.Connection = connection;

int i = (int)command.ExecuteNonQuery();

connection.Close();


More info and samples on: www.devarchweb.net

Propagate changes made in DataTable to SQL database with SqlParameters

// create and open connection
SqlConnection connection = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=basics;Integrated Security=True");

// create SQLCommand
SqlCommand command = new SqlCommand();
command.CommandText = "SELECT * FROM MY_SCHEMA.Persons";
command.Connection = connection;

// create DataTable with SqlAdapter and load data
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(command);
try
{
  connection.Open();
  int i = da.Fill(dt);

  dt.Rows[1]["Height"] = 187; // This line simulates user interaction. Imagine user changed height of the person in the first row.

  SqlCommand updateCommand = new SqlCommand();
  updateCommand.CommandText = "UPDATE MY_SCHEMA.Persons SET Height = @height WHERE id = @id";
  updateCommand.Connection = connection;
  updateCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 5, "id")); // this parameter identifies the record being updated (5 is max length))
  updateCommand.Parameters.Add(new SqlParameter("@height", SqlDbType.Int, 5, "Height")); // this parameter updates actual value

  da.UpdateCommand = updateCommand;
  da.Update(dt);
}
finally { connection.Close(); }





More info and samples on: www.devarchweb.net