数据库连接 使用sql语句 进行查询、删除、修改、添加
#region
/// <summary>
/// 连接数据库字符串
/// </summary>
private static string sqlconn ="server=192.168.3.95;database=dbname;user=sa;password=123456789";
#endregion
/// <summary>
/// 传入sql 返回相应数据表 查询
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="tableName">自定义表名</param>
/// <returns></returns>
public static DataTable GetDBData(string sql,string tableName)
{
DataTable dt = new DataTable();
SqlConnection my_conn;
try
{
my_conn = new SqlConnection(sqlconn); //用SqlConnection对象与指定的数据库相连接
my_conn.Open(); //打开数据库连接
SqlDataAdapter SQLda = new SqlDataAdapter(sql, my_conn); //创建一个SqlDataAdapter对象,并获取指定数据表的信息
SQLda.SelectCommand.CommandTimeout = 1800; //设置为半个小时,超时则报错
DataSet Ds = new DataSet(); //创建DataSet对象
SQLda.Fill(Ds, tableName); //通过SqlDataAdapter对象的Fill()方法,将数据表信息添加到DataSet对象中
dt = Ds.Tables[tableName];
if (my_conn.State == ConnectionState.Open) //判断是否打开与数据库的连接
{
my_conn.Close(); //关闭数据库的连接
my_conn.Dispose(); //释放My_con变量的所有空间
}
}
catch
{
System.Windows.Forms.MessageBox.Show("链接出错,请检查配置");
}
return dt;
}
/// <summary>
/// 修改 删除 增加
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static Boolean GetUpdateRes(string sql) {
Boolean resflag = false;
SqlConnection my_conn;
int num = 0;
try
{
my_conn = new SqlConnection(sqlconn); //用SqlConnection对象与指定的数据库相连接
my_conn.Open(); //打开数据库连接
SqlCommand SQLcom = new SqlCommand(sql, my_conn); //创建一个SqlCommand对象,用于执行SQL语句
num = SQLcom.ExecuteNonQuery(); //执行SQL语句
SQLcom.Dispose(); //释放所有空间
if (my_conn.State == ConnectionState.Open) //判断是否打开与数据库的连接
{
my_conn.Close(); //关闭数据库的连接
my_conn.Dispose(); //释放My_con变量的所有空间
}
if (num > 0) {
resflag = true;
}
}
catch
{
System.Windows.Forms.MessageBox.Show("链接出错,请检查配置");
}
return resflag;
}
数据库连接:使用存储过程
#region 通用存储过程
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
if (M_str_sqlcon == "")
{
M_str_sqlcon = System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
}
using (SqlConnection connection = new SqlConnection(M_str_sqlcon))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.SelectCommand.CommandTimeout = 60000; //超时设置
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
#endregion
#region 存储过程参数转换
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null && parameters.Length > 0)
{
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
}
return command;
}
#endregion