Tuesday, April 28, 2009

a simple method to manipulate data using sql using .net

public static DataTable ExecuteSQL(string sql, Dictionary parameters)
{
DataTable dt = new DataTable("DataTable");
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString))
{
myConnection.Open();
SqlCommand mySqlCommand = new SqlCommand(sql, myConnection);

if (parameters != null)
{
foreach (KeyValuePair p in parameters)
{
mySqlCommand.Parameters.AddWithValue(p.Key, p.Value);
}
}

using (SqlDataReader datareader = mySqlCommand.ExecuteReader())
{
dt.Load(datareader);
}
}
return dt;
}
//insert a tag record
Dictionary param = new Dictionary();
param.Add("@TagName", txtTagName.Text);
ExecuteSQL("INSERT TOP (200) INTO Tag(TagName) VALUES (@TagName)", param);