SQL Server安装包

image.png

MySQL安装包

image.png

全部代码

mysql和sql server的操作基本一致，唯一不一样的就是各种名称空间，类名不太一样，如：SqlCommand和MySqlCommand，Sql开头的变成MySql开头 这里连接的是sql server数据库

数据库链接字符串

  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. using System.Text;
  8. /// <summary>
  9. /// MSSQL数据库访问
  10. /// 
  11. /// </summary>
  12. public abstract class SqlHelper
  13. {
  14.     #region 连接字符串
  15.     private static string connectionString = "Data Source=.;Initial Catalog=S;Integrated Security=True";//连接字符串
  16.     #endregion
  19.     #region 初始化连接数据库
  20.     /// <summary>
  21.     /// 设置SqlCommand命令
  22.     /// </summary>
  23.     /// <param name="cmd">sql命令对象</param>
  24.     /// <param name="conn">sql连接对象</param>
  25.     /// <param name="trans">sql事务对象</param>
  26.     /// <param name="cmdText">sql语句</param>
  27.     /// <param name="cmdParms">sql参数</param>
  28.     private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
  29.     {
  30.         if (conn.State != ConnectionState.Open)
  31.             conn.Open();
  32.         cmd.Connection = conn;
  33.         cmd.CommandText = cmdText;
  34.         if (trans != null)
  35.             cmd.Transaction = trans;
  36.         cmd.CommandType = CommandType.Text;
  37.         if (cmdParms != null)
  38.         {
  39.             foreach (SqlParameter parm in cmdParms)
  40.                 cmd.Parameters.Add(parm);
  41.         }
  42.     }
  43.     #endregion
  45.     #region 执行操作
  46.     /*
  47.      * SqlParameter用法：
  48.      * 例如：string sql = "insert into T values(@name)";
  49.      *      SqlParameter name = new SqlParameter("name","取一个名字");
  50.      *      ExecuteSql(sql,name)
  51.      *      ExecuteSql(sql,new SqlParameter("name","取一个名字"))
  52.      *      没有参数可以不写cmdParms
  53.      */
  58.     /// <summary>
  59.     /// 执行sql操作，一般insert，update，delete
  60.     /// </summary>
  61.     /// <param name="sql">sql字符串</param>
  62.     /// <param name="cmdParms">sql参数，可以为空</param>
  63.     /// <returns>返回受影响的行数</returns>
  64.     public static int ExecuteSql(string sql, params SqlParameter[] cmdParms)
  65.     {
  66.         using(SqlConnection connection=new SqlConnection(connectionString))
  67.         {
  68.             using(SqlCommand cmd=new SqlCommand())
  69.             {
  70.                 try
  71.                 {
  72.                     PrepareCommand(cmd, connection, null, sql, cmdParms);
  73.                     int rows = cmd.ExecuteNonQuery();
  74.                     cmd.Parameters.Clear();
  75.                     return rows;
  76.                 }
  77.                 catch (System.Data.SqlClient.SqlException E)
  78.                 {
  79.                     throw new Exception(E.Message);
  80.                 }
  81.             }
  82.         }
  83.     }
  85.     /// <summary>
  86.     /// 得到第一行第一列的值，可用于计算count，max，min，avg,sum等
  87.     /// </summary>
  88.     /// <param name="sql">sql字符串</param>
  89.     /// <param name="cmdParms">sql参数，可以为空</param>
  90.     /// <returns>返回查询到的结果object类型</returns>
  91.     public static object GetFrist(string sql, params SqlParameter[] cmdParms)
  92.     {
  93.         using (SqlConnection connection = new SqlConnection(connectionString))
  94.         {
  95.             using (SqlCommand cmd = new SqlCommand())
  96.             {
  97.                 try
  98.                 {
  99.                     PrepareCommand(cmd, connection, null, sql, cmdParms);
  100.                     object obj = cmd.ExecuteScalar();
  101.                     cmd.Parameters.Clear();
  102.                     if (Equals(obj, null) || Equals(obj, System.DBNull.Value))//判断是否返回的值为空
  103.                     {
  104.                         return null;
  105.                     }
  106.                     else
  107.                     {
  108.                         return obj;
  109.                     }
  110.                 }
  111.                 catch (System.Data.SqlClient.SqlException e)
  112.                 {
  113.                     throw new Exception(e.Message);
  114.                 }
  115.             }
  116.         }
  117.     }
  119.     /// <summary>
  120.     /// 得到一个reader对象，遍历这个对象获得数据
  121.     /// 用法：while(reader.Read())
  122.     ///     {
  123.     ///         int id=reader.GetInt32(0);//列的索引
  124.     ///         int id=(int)reader[0]
  125.     ///         int id=(int)reader["id"]
  126.     ///     }
  127.     /// </summary>
  128.     /// <param name="sql">sql字符串</param>
  129.     /// <param name="cmdParms">sql参数，可以为空</param>
  130.     /// <returns>reader对象</returns>
  131.     public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] cmdParms)
  132.     {
  133.        using (SqlConnection connection = new SqlConnection(connectionString))
  134.         {
  135.             using (SqlCommand cmd = new SqlCommand())
  136.             {
  137.                 try
  138.                 {
  139.                     PrepareCommand(cmd, connection, null, sql, cmdParms);
  140.                     SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  141.                     cmd.Parameters.Clear();
  142.                     return reader;
  143.                 }
  144.                 catch (System.Data.SqlClient.SqlException e)
  145.                 {
  146.                     throw new Exception(e.Message);
  147.                 }
  148.             }
  149.         }
  150.     }
  153.     /// <summary>
  154.     /// 得到一个DataTable对象，实现查询数据
  155.     /// 用法：dt.Rows[5][0]或者dt.Rows[5]["id"]等
  156.     /// </summary>
  157.     /// <param name="sql">sql字符串</param>
  158.     /// <param name="cmdParms">sql参数，可以为空</param>
  159.     /// <returns>返回一个datatable对象</returns>
  160.     public static DataTable GetDataTable(string sql, params SqlParameter[] cmdParms)
  161.     {
  162.         using (SqlConnection connection = new SqlConnection(connectionString))
  163.         {
  164.             using(SqlCommand cmd = new SqlCommand())
  165.             {
  166.                 PrepareCommand(cmd, connection, null, sql, cmdParms);
  167.                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
  168.                 {
  169.                     DataTable dt = new DataTable();
  170.                     try
  171.                     {
  172.                         da.Fill(dt);
  173.                         cmd.Parameters.Clear();
  174.                         return dt;
  175.                     }
  176.                     catch (System.Data.SqlClient.SqlException ex)
  177.                     {
  178.                         throw new Exception(ex.Message);
  179.                     }
  180.                 }
  181.             }
  182.         }
  183.     }
  185.     /// <summary>
  186.     /// 执行sql事务
  187.     /// 确保事务发生的一致性，都成功了就提交，失败异常就回滚
  188.     /// 如果没有参数，哈希表的value写null
  189.     /// 执行sql如果返回0行也叫执行成功了
  190.     /// </summary>
  191.     /// <param name="SQLStringList">哈希表，key为sql语句，value为SqlParameter[]</param>
  192.     public static void ExecuteSqlTran(Hashtable SQLStringList)
  193.     {
  194.         using (SqlConnection conn = new SqlConnection(connectionString))
  195.         {
  196.             conn.Open();
  197.             using (SqlTransaction trans = conn.BeginTransaction())
  198.             {
  199.                 using(SqlCommand cmd = new SqlCommand())
  200.                 {
  201.                     try
  202.                     {
  203.                         //循环
  204.                         foreach (DictionaryEntry myDE in SQLStringList)
  205.                         {
  206.                             string sql = myDE.Key.ToString();
  207.                             SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
  208.                             PrepareCommand(cmd, conn, trans, sql, cmdParms);
  209.                             int val = cmd.ExecuteNonQuery();
  210.                             cmd.Parameters.Clear();
  211.                         }
  212.                         trans.Commit();
  213.                     }
  214.                     catch
  215.                     {
  216.                         trans.Rollback();
  217.                         throw;
  218.                     }
  219.                 }
  220.             }
  221.         }
  222.     }
  225.     /// <summary>
  226.     /// 判断是否存在记录
  227.     /// </summary>
  228.     /// <param name="sql">sql语句</param>
  229.     /// <param name="cmdParms">sql参数，可以为空</param>
  230.     /// <returns></returns>
  231.     public static bool Exists(string sql, params SqlParameter[] cmdParms)
  232.     {
  233.         try
  234.         {
  235.             if (GetDataTable(sql, cmdParms).Rows.Count > 0)
  236.             {
  237.                 return true;
  238.             }
  239.             else
  240.             {
  241.                 return false;
  242.             }
  243.         }
  244.         catch (System.Data.SqlClient.SqlException E)
  245.         {
  246.             throw new Exception(E.Message);
  247.         }
  248.     }
  250.     #endregion
  252. }