using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Text; /// <summary> /// 数据库工具 /// </summary> public class DatabaseUtil { /// <summary> /// 数据库连接语句 /// </summary> private static string strConn = "数据库链接语句"; /// <summary> /// 执行sql查询语句 /// </summary> /// <param name="strSql"></param> /// <param name="strError"></param> /// <returns></returns> public static DataSet ExecSqlSelect(string strSql, ref string strError) { try { DataSet resDs = new DataSet(); SqlConnection con = new SqlConnection(strConn); con.Open(); SqlDataAdapter sda = new SqlDataAdapter(strSql, con); sda.Fill(resDs); con.Close(); sda.Dispose(); return resDs; } catch (Exception ex) { strError = ex.Message + "\n" + ex.StackTrace; } return null; } /// <summary> /// 插入 /// </summary> /// <param name="tableName"></param> /// <param name="argDic"></param> /// <param name="strError"></param> /// <returns></returns> public static int ExectSqlInsert(string tableName, Dictionary<string, string> argDic, ref string strError) { try { string strArgName = string.Empty; string strArgValue = string.Empty; Dictionary<string, string>.Enumerator it = argDic.GetEnumerator(); while (it.MoveNext()) { strArgName += it.Current.Key + ","; strArgValue += "\‘" + it.Current.Value + "\‘" + ","; } strArgName = strArgName.Substring(0, strArgName.Length - 1); strArgValue = strArgValue.Substring(0, strArgValue.Length - 1); string strSql = "insert into " + tableName + " (" + strArgName + ") values (" + strArgValue + ")"; SqlConnection con = new SqlConnection(strConn); con.Open(); //操作数据库的工具SqlCommand SqlCommand cmd = new SqlCommand(strSql, con); int i = cmd.ExecuteNonQuery(); con.Close(); cmd.Dispose(); return i; } catch (Exception ex) { strError = ex.Message + "\n" + ex.StackTrace; return 0; } } /// <summary> /// 添加、删除、修改 /// </summary> /// <param name="strSql"></param> /// <param name="strError"></param> /// <returns></returns> public static int ExectSqlData(string strSql, ref string strError) { try { SqlConnection con = new SqlConnection(strConn); con.Open(); //操作数据库的工具SqlCommand SqlCommand cmd = new SqlCommand(strSql, con); int i = cmd.ExecuteNonQuery(); con.Close(); cmd.Dispose(); return i; } catch (Exception ex) { strError = ex.Message + "\n" + ex.StackTrace; return 0; } } /// <summary> /// 获取dataSet /// </summary> /// <param name="procName">存储过程名字</param> /// <param name="parirsDic">返回</param> /// <returns>返回dataset</returns> public static DataSet ExecProc(string procName, Dictionary<string, object> parirsDic) { SqlConnection conn = null; try { conn = new SqlConnection(strConn); Console.WriteLine("数据库连接成功!"); //参数集 Dictionary<string, SqlParameter> argDic = new Dictionary<string, SqlParameter>();//执行存储过程的 if (parirsDic != null && parirsDic.Count > 0) { Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator(); while (it.MoveNext()) { SqlParameter tempPar = new SqlParameter(it.Current.Key, it.Current.Value); argDic.Add(it.Current.Key, tempPar); } } SqlCommand cmd = new SqlCommand(procName, conn); Dictionary<string, SqlParameter>.Enumerator it2 = argDic.GetEnumerator(); while (it2.MoveNext()) { cmd.Parameters.Add(it2.Current.Value); } cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter dap = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //SqlCommandBuilder sqlBulider = new SqlCommandBuilder(dap); dap.Fill(ds); dap.Dispose(); return ds; } catch (Exception ex) { throw ex; } finally { if (conn != null) { //关闭数据库连接 conn.Close(); } } } /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">存储过程名字</param> /// <param name="parirsDic">输入参数</param> /// <param name="outPutDic">输出参数</param> /// <returns></returns> public static int ExecProc(string procName, Dictionary<string, object> parirsDic, ref Dictionary<string, object> outPutDic) { SqlConnection conn = null; try { conn = new SqlConnection(strConn); Console.WriteLine("数据库连接成功!"); //参数集 Dictionary<string, SqlParameter> argDic = new Dictionary<string, SqlParameter>();//执行存储过程的 if (parirsDic != null && parirsDic.Count > 0) { Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator(); while (it.MoveNext()) { SqlParameter tempPar = new SqlParameter(it.Current.Key, it.Current.Value); argDic.Add(it.Current.Key, tempPar); } } //设置错误代码返回值 int errorId = 1000; SqlParameter returnPara = new SqlParameter("@return", errorId); returnPara.Direction = ParameterDirection.ReturnValue; argDic.Add("@return", returnPara); if (outPutDic != null && outPutDic.Count > 0) { Dictionary<string, object>.Enumerator it = outPutDic.GetEnumerator(); while (it.MoveNext()) { SqlParameter tempPar = new SqlParameter(it.Current.Key, it.Current.Value); tempPar.Direction = ParameterDirection.Output; argDic.Add(it.Current.Key, tempPar); } } SqlCommand cmd = new SqlCommand(procName, conn); Dictionary<string, SqlParameter>.Enumerator it2 = argDic.GetEnumerator(); while (it2.MoveNext()) { cmd.Parameters.Add(it2.Current.Value); } cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter dap = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); dap.Fill(dt); dap.Dispose(); errorId = Convert.ToInt32(argDic["@return"].Value); return errorId; } catch (Exception ex) { Console.WriteLine("数据库连接失败!" + ex.Message); return 2000;//后端执行异常 } finally { if (conn != null) { //关闭数据库连接 conn.Close(); } } } /// <summary> /// 获取数据 /// </summary> /// <param name="procName">存储过程名字</param> /// <param name="parirsDic">参数列表</param> /// <returns></returns> public static DataTable GetData(string procName, Dictionary<string, object> parirsDic) { SqlConnection conn = null; try { conn = new SqlConnection(strConn); conn.Open(); //打开数据库连接 //conn.Open(); Console.WriteLine("数据库连接成功!"); List<SqlParameter> parasList = new List<SqlParameter>(); if (parirsDic != null && parirsDic.Count > 0) { Dictionary<string, object>.Enumerator it = parirsDic.GetEnumerator(); while (it.MoveNext()) { parasList.Add(new SqlParameter(it.Current.Key, it.Current.Value)); } } SqlCommand cmd = new SqlCommand(procName, conn); for (int i = 0; i < parasList.Count; i++) { cmd.Parameters.Add(parasList[i]); } cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter dap = new SqlDataAdapter(cmd); DataTable ds = new DataTable(); dap.Fill(ds); return ds; } catch (Exception ex) { Console.WriteLine("数据库连接失败!" + ex.Message); throw ex; } finally { if (conn != null) { //关闭数据库连接 conn.Close(); } } } }