using System.Data.SqlClient;
using System.Configuration;
引用：System.Configuration
连接字符串放到配置文件中
新建一个类,写如下些方法,进行数据的操作
// ExecuteNonQuery 构建
public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)
{
string Str = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(Str))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
foreach (SqlParameter par in parameters)
{
cmd.Parameters.Add(par);
}
return cmd.ExecuteNonQuery();
}
} } // ExecuteNonQuery 方法的使用
private void button1_Click(object sender, EventArgs e) //单击事件
{
try
{
SQLHelper.ExecuteNonQuery("insert into T_Age(Name,Age) values(@N,@A)", new SqlParameter("@N", txtLogin.Text.Trim()), new SqlParameter("@A", txtAge.Text.Trim()));
MessageBox.Show("插入成功！");
}
catch (Exception ex)
{
MessageBox.Show(string.Format("插入失败！:{0}", ex));
return;
}
} //-----------------------------------------------------------------------------
// ExecuteScalar 构建
public static object ExeccuteScalar(string sql, params SqlParameter[] parameters)
{
string Str = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(Str))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (SqlParameter par in parameters)
{
cmd.Parameters.Add(par);
}
return cmd.ExecuteScalar();
}
} // ExecuteScalar 方法的使用
private void button2_Click(object sender, EventArgs e) //单击事件
{
int i = Convert.ToInt32(SQLHelper.ExeccuteScalar("select count(*) T_phone"));
MessageBox.Show(Convert.ToString(i));
}
//----------------------------------------------------------------------------- // ExecuteReader 构造 // 不能使用 using
public static SqlDataReader SqlDataReader(string sql, params SqlParameter[] parameters)
{
string Str = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
SqlConnection conn = new SqlConnection(Str);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (SqlParameter par in parameters)
{
cmd.Parameters.Add(par);
}
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
} // ExecuteReader 方法的使用
private void button4_Click(object sender, EventArgs e) //单击事件
{
SqlDataReader dr = SQLHelper.SqlDataReader_using("select * from T_Age");
while (dr.Read())
{
MessageBox.Show(Convert.ToString(dr["Name"])); //读取Name字段的值
}
} //-----------------------------------------------------------------------------
// ExecuteDataSet 的构建
public static DataTable ExecuteDataSet(string sql, params SqlParameter[] SqlParmeter)
{
string Str = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(Str))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (SqlParameter par in SqlParmeter) //遍历增加 SqlParmeter 参数
{
cmd.Parameters.Add(par);
}
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
return dataset.Tables[];
}
} // ExecuteDataSet 方法的使用
private void button5_Click(object sender, EventArgs e)
{
DataTable dt = SQLHelper.ExecuteDataSet("select * from T_Age");
for (int i = ; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string name =Convert.ToString(row["Name"]);
MessageBox.Show(name);
}
} //-----------------------------------------------------------------------------
注意：SqlDataReader如果用 using 的话，将执行不了，因为在读取数据的时候是在using以外,无法读取
如下是错误的代码 public static SqlDataReader ExecuteReader_using(string sql, params SqlParameter[] parameters)
{
string Str = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(Str))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
foreach (SqlParameter par in parameters)
{
cmd.Parameters.Add(par);
}
return cmd.ExecuteReader();
}
}
//-----------------------------------------------------------------------------