Connect to SQl server database

Connect to SQl server database

Using System.Data.OleDb;
//////////////////
SqlConnection objConn = new SqlConnection("Server=servername;Database=databasename;User ID=username;Password=Password");

SqlCommand objCmd = new SqlCommand("select * from table", objConn);
SqlDataAdapter adapt = new SqlDataAdapter(objCmd);
DataSet dataS = new DataSet();
adapt.Fill(dataS);
////////////////
Converting a DateTime
Converting a DateTime fields from SQl to shore date string
((DateTime)dt.Rows[0]["date4"]).ToShortDateString();

Using DataTable.ImportRow Method

This small code shows how to use the DataTable.ImportRow Method to copy rows from a table to another table.

The ImportRow method of DataTable copies a row into a DataTable with all of the properties and data of the row. It actually calls NewRow method on destination DataTable with current table schema and sets DataRowState to Added.

DataTable dt; /// fill the table before you use it
DataTable copyto;

foreach(DataRow dr in dt.Rows)
{
copyto.ImportRow(dr);
}


the above code copies all the rows of the table dt to the table copyto

convert a datatable to a dataset



DataSet ds = new DataSet("table");
ds.Tables.Add(dt);

Deleting a Rows in a DataTable




foreach (DataRow row in dummyDt.Rows)
{
if(row["column"] == "delete")
{
dr.delete();
dt.AcceptChanges();
}
}



Adding a DataTable to Another Datatable





foreach (DataRow row in dummyDt.Rows)
{
DataRow newRow = dt.NewRow();
newRow.ItemArray = row.ItemArray;
dt.Rows.Add(newRow);
dt.AcceptChanges();
}