using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.SqlClient; using System.Data; namespace 数据链接 { public static class SqlServer { #region 样本 private static readonly string defaultConnectString = //"Server = 192.168.100.91;" + //"Database = LZ_ZLGC;" + //"User ID = sa;" + //"Password = info12345"; "Server = INFO6F5QNJ2;" + "Database =RM_Db;" + "Trusted_Connection=SSPI"; #endregion #region 变量 private static SqlConnection _con = null; public static string _constr = // "Server = 192.168.100.91;" + //"Database = LZ_ZLGC;" + //"User ID = sa;" + //"Password = info12345;"; "Server = INFO6F5QNJ2;" + "Database =RM_Db;" + "Trusted_Connection=SSPI"; #endregion #region 属性 public static string constr { get { if (_constr == null || _constr.Equals(String.Empty)) { _constr = defaultConnectString; } return _constr; } set { _constr = value; } } /// <summary> /// 获取或设置数据库连接对象 /// </summary> public static SqlConnection Con { get { if (SqlServer._con == null) { SqlServer._con = new SqlConnection(); } if (SqlServer._con.ConnectionString == null || SqlServer._con.ConnectionString.Equals(string.Empty)) { SqlServer._con.ConnectionString = SqlServer.constr; } return SqlServer._con; } set { SqlServer._con = value; } } #endregion /// <summary> /// 获取数据表 /// </summary> /// <param name="commandText">select命令</param> /// <param name="param">参数表</param> /// <returns></returns> #region MyRegion public static DataTable GetDataTable(string commandText, params SqlParameter[] param) { DataTable result = new DataTable(); try { using (SqlCommand cmd = new SqlCommand(commandText, SqlServer.Con)) { if (param != null) cmd.Parameters.AddRange(param); try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(result); } catch (Exception ex) { result = null; } } } finally { if (SqlServer.Con.State != ConnectionState.Closed) { SqlServer.Con.Close(); } } return result; } #endregion /// <summary> /// 执行不查询的数据库操作 /// </summary> /// <param name="commandText">Oracle语句或存储过程名</param> /// <param name="commandType">Oracle命令类型</param> /// <param name="param">Oracle命令参数数组</param> /// <returns>受影响的行数</returns> #region MyRegion public static int ExecuteNonQuery(string commandText, CommandType commandType, params SqlParameter[] param) { int result = 0; try { using (SqlCommand cmd = new SqlCommand(commandText, SqlServer.Con)) { try { //cmd.CommandType = commandType; if (param != null) { cmd.Parameters.AddRange(param); } SqlServer.Con.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { result = -1; } } } finally { if (SqlServer.Con.State != ConnectionState.Closed) { SqlServer.Con.Close(); } } return result; } #endregion } }
调用
/// <summary> /// SqlServer数据库增删改查 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { DataTable dt = new DataTable();//DataTable接收传回的数据 dt = SqlServer.GetDataTable("select * from Base_UserInfo;", null);//查询 // SqlServer.ExecuteNonQuery(@"INSERT INTO ZLCB01A (ZLCB01A005) VALUES('912')", CommandType.Text, null);//增加 //SqlServer.ExecuteNonQuery("delete from ZLCB01A WHERE ZLCB01A005='955'", CommandType.Text, null);//删除 //SqlServer.ExecuteNonQuery(@"update ZLCB01A SET ZLCB01A005='955' where ZLCB01A005='912'", CommandType.Text, null);//修改 }