写在前面:
在日常的工作中,通常一个项目会大量用的数据库的各种基本操作,因此小编几个常见的数据库的操作封装成了一个dll方便后续的开发使用。SQLserver数据库是最为常见的一种数据库,本文则主要是记录了C#对SQL的连接、增、删、改、查的基本操作,如有什么问题还请各位大佬指教。后续也将对其他几个常用的数据库进行相应的整理。话不多说,直接开始码代码。
先声明一个SqlConnection便于后续使用。
private SqlConnection sql_con;//声明一个SqlConnection
sql打开:
/// <summary> /// SQLserver open /// </summary> /// <param name="link">link statement</param> /// <returns>Success:success; Fail:reason</returns> public string Sqlserver_Open(string link) { try {
sql_con = new SqlConnection(link);
sql_con.Open();
return "success";
}
catch (Exception ex)
{
return ex.Message;
}
}
sql关闭:
/// <summary> /// SQLserver close /// </summary> /// <returns>Success:success Fail:reason</returns> public string Sqlserver_Close() { try { if (sql_con == null) { return "No database connection"; } if (sql_con.State == ConnectionState.Open || sql_con.State == ConnectionState.Connecting) { sql_con.Close(); sql_con.Dispose(); } else { if (sql_con.State == ConnectionState.Closed) { return "success"; } if (sql_con.State == ConnectionState.Broken) { return "ConnectionState:Broken"; } } return "success"; } catch (Exception ex) { return ex.Message; } }
sql的增删改:
/// <summary> /// SQLserver insert,delete,update /// </summary> /// <param name="sql">insert,delete,update statement</param> /// <returns>Success:success + Number of affected rows; Fail:reason</returns> public string Sqlserver_Insdelupd(string sql) { try { int num = 0; if (sql_con == null) { return "Please open the database connection first"; } if (sql_con.State == ConnectionState.Open) { SqlCommand sqlCommand = new SqlCommand(sql, sql_con); num = sqlCommand.ExecuteNonQuery(); } else { if (sql_con.State == ConnectionState.Closed) { return "Database connection closed"; } if (sql_con.State == ConnectionState.Broken) { return "Database connection is destroyed"; } if (sql_con.State == ConnectionState.Connecting) { return "The database is in connection"; } } return "success" + num; } catch (Exception ex) { return ex.Message.ToString(); } }
sql的查:
/// <summary> /// SQLserver select /// </summary> /// <param name="sql">select statement</param> /// <param name="record">Success:success; Fail:reason</param> /// <returns>select result</returns> public DataSet Sqlserver_Select(string sql, out string record) { try { DataSet dataSet = new DataSet(); if (sql_con == null) { record = "Please open the database connection first"; return dataSet; } if (sql_con.State == ConnectionState.Open) { SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sql_con); sqlDataAdapter.Fill(dataSet, "sample"); sqlDataAdapter.Dispose(); record = "success"; return dataSet; } if (sql_con.State == ConnectionState.Closed) { record = "Database connection closed"; return dataSet; } if (sql_