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