1.首先要获取数据库的连接字符串,可以通过visual studio的工具->连接到数据库来获得。
连接字符串格式:
Data Source=********;Initial Catalog=********;Persist Security Info=True;User ID=***;Password=****
2.新建SqlHelper类
public static class SqlHelper
{
//定义连接数据库的配置字符串,此处记得替换成你数据库的连接字符串
private static string connectionString = "Data Source=********;Initial Catalog=********;Persist Security Info=True;User ID=***;Password=****";
/// <summary>
/// 执行增、删、改的方法
/// </summary>
/// <param name="sql">预计执行的非SELECT查询语句</param>
/// <param name="param">SQL语句中的可变参数</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(param);
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("异常信息:\n" + ex.Message);
return -2;
}
}
}
/// <summary>
/// 执行查询操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns>返回查询多列</returns>
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connectionString);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(param);
//设置CommandBehavior.CloseConnection后,关闭SqlDataReader时会自动关闭使用的连接。
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行查询操作,例如查询询记录的数量
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns>返回单个数据对象,只返回第一行第一列的数据,结果需要强制转化</returns>
public static Object ExecuteScalar(string sql, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(param);
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
}
}
}