/// <summary> /// 分页获取数据列表 /// </summary> /// <param name="Order_Field">排序字段</param> /// <param name="OrderType">排序类型(1:升序 0:降序)</param> /// <param name="PageSize">页大小</param> /// <param name="PageIndex">页码</param> /// <param name="strWhere">查询条件</param> /// <param name="TotalCount">返回总数</param> /// <returns>DataTable</returns> // public DataTable GetDataListBuPagerQueryParam(PagerQueryParam pager, out int TotalCount, out String ErrMsg) //{ public DataTable GetDataByPagerQueryParam(PagerQueryParam pager) { //TotalCount = pager.TotalCount; //ErrMsg = string.Empty; DataTable dt = new DataTable(); //if (pager == null) //{ // ErrMsg = "出入参数PagerQueryParam为NULL"; // return dt; //} try { SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); DBCallCommon.PrepareStoredProc(sqlConn, sqlCmd, "procpaging"); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Table_Name", pager.TableName, SqlDbType.VarChar, 100); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Primary_Key", pager.PrimaryKey, SqlDbType.VarChar, 100); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Show_Fields", pager.ShowFields, SqlDbType.VarChar, 100); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Order_Field", pager.OrderField, SqlDbType.VarChar, 100); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@OrderType", pager.OrderType.ToString(), SqlDbType.VarChar, 100); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@where_Condition", pager.StrWhere, SqlDbType.VarChar, 300); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Page_Size", pager.PageSize.ToString(), SqlDbType.Int, 20); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Page_Index", pager.PageIndex.ToString(), SqlDbType.Int, 20); //DBCallCommon.AddParameterToStoredProc(sqlCmd, "@TotalCount", pager.TotalCount.ToString(), SqlDbType.Int, 20); //DBCallCommon.AddParameterToStoredProc(sqlCmd, "@ReTotalCount", "10", SqlDbType.Int, 20); dt = DBCallCommon.GetDataTableUsingCmd(sqlCmd); sqlConn.Close(); //return dt; //DbCommand dbCommand = db.GetStoredProcCommand("SMSPagination"); //db.AddInParameter(dbCommand, "Table_Name", DbType.AnsiString, pager.TableName); //db.AddInParameter(dbCommand, "Primary_Key", DbType.AnsiString, pager.PrimaryKey); //db.AddInParameter(dbCommand, "Show_Fields", DbType.AnsiString, pager.ShowFields); //db.AddInParameter(dbCommand, "Order_Field", DbType.AnsiString, pager.OrderField); //db.AddInParameter(dbCommand, "OrderType", DbType.Boolean, pager.OrderType); //db.AddInParameter(dbCommand, "where_Condition", DbType.String, pager.StrWhere); //db.AddInParameter(dbCommand, "Page_Size", DbType.Int32, pager.PageSize); //db.AddInParameter(dbCommand, "Page_Index", DbType.Int32, pager.PageIndex); //db.AddInParameter(dbCommand, "TotalCount", DbType.Int32, pager.TotalCount); //db.AddOutParameter(dbCommand, "ReTotalCount", DbType.Int32, 10); //using (DataSet ds = db.ExecuteDataSet(dbCommand)) //{ // if (pager.PageIndex == 1) // { // if (db.GetParameterValue(dbCommand, "ReTotalCount") != DBNull.Value && db.GetParameterValue(dbCommand, "TotalCount") != null) // { // TotalCount = Convert.ToInt32((db.GetParameterValue(dbCommand, "ReTotalCount"))); // } // } // dt = ds.Tables[0]; //} } catch (Exception) { throw; } return(dt); }
/// <summary> /// 执行事务 /// </summary> /// <param name="sqlTexts"></param> public static void ExecuteTrans(List <string> sqlTexts) { SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); sqlConn.Open(); //启用事务 SqlTransaction sqlTran = sqlConn.BeginTransaction(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.Transaction = sqlTran; sqlCmd.CommandTimeout = 0; try { foreach (string sqlText in sqlTexts) { sqlCmd.CommandText = sqlText; sqlCmd.ExecuteNonQuery(); } sqlTran.Commit(); } catch (Exception) { sqlTran.Rollback(); throw; } finally { closeConn(sqlConn); } }
/// <summary> /// 使用sql语句获取DataTable /// </summary> /// <param name="sqlText"></param> /// <returns></returns> public static DataTable GetDTUsingSqlText(string sqlText) { SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); try { openConn(sqlConn); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = sqlText; sqlCmd.CommandTimeout = 1000; DataTable dt = GetDataTableUsingCmd(sqlCmd); sqlConn.Close(); return(dt); } catch (Exception) { throw; } finally { closeConn(sqlConn); } }
/// <summary> /// 分页获取数据列表 /// </summary> /// <param name="Order_Field">排序字段</param> /// <param name="OrderType">排序类型(1:升序 0:降序)</param> /// <param name="PageSize">页大小</param> /// <param name="PageIndex">页码</param> /// <param name="strWhere">查询条件</param> /// <returns>DataTable</returns> public static DataTable GetDataByPagerQueryParamGroupBy(PagerQueryParamGroupBy pager) { DataTable dt = new DataTable(); try { SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); DBCallCommon.PrepareStoredProc(sqlConn, sqlCmd, "procpagingGroupBy"); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Table_Name", pager.TableName, SqlDbType.VarChar, 1000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Primary_Key", pager.PrimaryKey, SqlDbType.VarChar, 1000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Show_Fields", pager.ShowFields, SqlDbType.VarChar, 8000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Order_Field", pager.OrderField, SqlDbType.VarChar, 1000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Group_Field", pager.GroupField, SqlDbType.VarChar, 1000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@OrderType", pager.OrderType.ToString(), SqlDbType.VarChar, 1000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@where_Condition", pager.StrWhere, SqlDbType.VarChar, 3000); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Page_Size", pager.PageSize.ToString(), SqlDbType.Int, 200); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@Page_Index", pager.PageIndex.ToString(), SqlDbType.Int, 200); dt = DBCallCommon.GetDataTableUsingCmd(sqlCmd); sqlConn.Close(); } catch (Exception) { throw; } return(dt); }
/// <summary> /// 分页获取数据列表 /// </summary> /// <param name="Order_Field">排序字段</param> /// <param name="OrderType">排序类型(1:升序 0:降序)</param> /// <param name="PageSize">页大小</param> /// <param name="PageIndex">页码</param> /// <param name="strWhere">查询条件</param> /// <returns>DataTable</returns> public static DataTable GetDataByPagerQueryParam1(PagerQueryParam pager) { DataTable dt = new DataTable(); try { SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); DBCallCommon.PrepareStoredProc(sqlConn, sqlCmd, "SplitPage"); sqlCmd.Parameters.Add("@ExecSql", SqlDbType.VarChar, 10000); sqlCmd.Parameters["@ExecSql"].Value = pager.TableName; sqlCmd.Parameters.Add("@CurrentPage", SqlDbType.Int); sqlCmd.Parameters["@CurrentPage"].Value = pager.PageIndex; sqlCmd.Parameters.Add("@PageSize", SqlDbType.Int); sqlCmd.Parameters["@PageSize"].Value = pager.PageSize; dt = DBCallCommon.GetDataTableUsingCmd(sqlCmd); sqlConn.Close(); } catch (Exception) { throw; } return(dt); }
public static DataTable Projects_Select(int PageNumber, int PageSize) { System.Data.SqlClient.SqlConnection sqlConn = new SqlConnection(); System.Data.SqlClient.SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); DBCallCommon.PrepareStoredProc(sqlConn, sqlCmd, "procgetprojectsbypage"); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@PageNumber", PageNumber.ToString(), SqlDbType.Int, 20); DBCallCommon.AddParameterToStoredProc(sqlCmd, "@PageSize", PageSize.ToString(), SqlDbType.Int, 20); DataTable dt = DBCallCommon.GetDataTableUsingCmd(sqlCmd); sqlConn.Close(); return(dt); }
/// <summary> /// 根据comText查询数据库,将结果填入DataSet并返回 </summary> /// <param name="ComText">SQL语句</param> /// <returns>填充后的dataset</returns> public static DataSet FillDataSet(String comText) { SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); SqlDataAdapter sda = new SqlDataAdapter(comText, sqlConn); DataSet ds = new DataSet(); ds.Tables.Clear(); openConn(sqlConn); sda.Fill(ds); closeConn(sqlConn); return(ds); }
/// <summary> /// 通过工程ID删除工程 by ZJH 2011/3/19 /// </summary> /// <param name="pj_ID"></param> /// <returns></returns> public static int DeleteENGByENG_ID(string eng_ID) { string conStr = DBCallCommon.GetStringValue("connectionStrings"); SqlConnection conn = new SqlConnection(conStr); SqlCommand sc = new SqlCommand(); sc.CommandType = CommandType.Text; sc.Connection = conn; sc.CommandText = "DELETE FROM TBPM_ENGINFO WHERE ENG_ID = '" + eng_ID + "'"; openConn(conn); int rowEffected = sc.ExecuteNonQuery(); closeConn(conn); return(rowEffected); }
/******************************* * 操作SQL的查询语句 * ****************************/ public DataTable _GetSqlDataTable(string sqlText) { //Trace.Write(sqlText); DataTable dt = new DataTable(); SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = sqlText; dt = DBCallCommon.GetDataTableUsingCmd(sqlCmd); return(dt); }
[WebMethod] //合同号 public String[] GetContractNO(string prefixText, int count) { ///检测参数是否为空 if (string.IsNullOrEmpty(prefixText) == true || count <= 0) { return(null); } // 如果数组为空 if (autoCompleteWordList == null) { //读取数据库的内容 SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); sqlConn.Open(); DataSet ds = new DataSet(); string sqltext = ""; //2为供应商 sqltext = "SELECT PCON_BCODE+'|'+PCON_NAME AS HTBH FROM TBPM_CONPCHSINFO " + " WHERE (PCON_BCODE LIKE '%" + prefixText + "%' or PCON_NAME like '%" + prefixText + "%')"; ds = DBCallCommon.FillDataSet(sqltext); //读取内容文件的数据到临时数组 string[] temp = new string[ds.Tables[0].Rows.Count]; int i = 0; foreach (DataRow dr in ds.Tables[0].Rows) { temp[i] = dr["HTBH"].ToString(); i++; } Array.Sort(temp, new CaseInsensitiveComparer()); //将临时数组的内容赋给返回数组 autoCompleteall = temp; if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } } int matchCount = autoCompleteall.Length >= count ? count : autoCompleteall.Length; string[] matchResultList = new string[matchCount]; if (matchCount > 0) { //复制搜索结果 Array.Copy(autoCompleteall, 0, matchResultList, 0, matchCount); } return(matchResultList); }
public string[] get_rwh_th(string prefixText, int count, string contextKey) { ///检测参数是否为空 if (string.IsNullOrEmpty(prefixText) == true || count <= 0) { return(null); } // 如果数组为空 if (autoCompleteWordList == null) { //读取数据库的内容 SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); sqlConn.Open(); System.Data.DataTable dt = new System.Data.DataTable(); string sqltext = ""; //2为供应商 sqltext = "SELECT distinct BM_TUHAO AS Expr1 FROM TBPM_STRINFODQO WHERE " + "(BM_TUHAO like '%" + prefixText + "%') AND BM_ENGID='" + contextKey + "' ORDER BY BM_TUHAO"; dt = DBCallCommon.GetDTUsingSqlText(sqltext); //读取内容文件的数据到临时数组 string[] temp = new string[dt.Rows.Count]; int i = 0; foreach (DataRow dr in dt.Rows) { temp[i] = dr["Expr1"].ToString(); i++; } Array.Sort(temp, new CaseInsensitiveComparer()); //将临时数组的内容赋给返回数组 autoCompleteall = temp; if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } } int matchCount = autoCompleteall.Length >= count ? count : autoCompleteall.Length; string[] matchResultList = new string[matchCount]; if (matchCount > 0) { //复制搜索结果 Array.Copy(autoCompleteall, 0, matchResultList, 0, matchCount); } return(matchResultList); }
[WebMethod] //工程名称 public String[] GetENGNAME(string prefixText, int count) { ///检测参数是否为空 if (string.IsNullOrEmpty(prefixText) == true || count <= 0) { return(null); } // 如果数组为空 if (autoCompleteWordList == null) { //读取数据库的内容 SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); sqlConn.Open(); DataSet ds = new DataSet(); DataTable glotb = new DataTable(); ds = DBCallCommon.FillDataSet("SELECT ISNULL(TSA_ENGNAME,'') + '|' + ISNULL(TSA_ID,'') as Expr1 FROM TBPM_TCTSASSGN WHERE (TSA_ENGNAME LIKE '%" + prefixText + "%' or TSA_ID like '%" + prefixText + "%') and TSA_ID not like '%-%'"); glotb.Merge(ds.Tables[0]); glotb.AcceptChanges(); //读取内容文件的数据到临时数组 string[] temp = new string[glotb.Rows.Count]; int i = 0; foreach (DataRow dr in glotb.Rows) { temp[i] = dr["Expr1"].ToString(); i++; } Array.Sort(temp, new CaseInsensitiveComparer()); autoCompleteall = temp; //将临时数组的内容赋给返回数组 if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } } int matchCount = autoCompleteall.Length >= count ? count : autoCompleteall.Length; string[] matchResultList = new string[matchCount]; if (matchCount > 0) { //复制搜索结果 Array.Copy(autoCompleteall, 0, matchResultList, 0, matchCount); } return(matchResultList); }
/// <summary> /// 使用sql语句获取SqlDataReader /// </summary> /// <param name="sqlText"></param> /// <returns></returns> public static SqlDataReader GetDRUsingSqlText(string sqlText) { SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); try { sqlCmd.Connection = sqlConn; sqlCmd.CommandText = sqlText; SqlDataReader dr = GetDataReader(sqlConn, sqlCmd); return(dr); } catch (Exception) { throw; } }
/// <summary> /// 执行SQL语句,返回受影响行数值 /// </summary> /// <param name="sqlText"></param> /// <returns></returns> public static int ExeSqlTextGetInt(string sqlText) { SqlConnection sqlConn = new SqlConnection(); SqlCommand sqlCmd = new SqlCommand(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); try { openConn(sqlConn); sqlCmd.Connection = sqlConn; sqlCmd.CommandText = sqlText; sqlCmd.CommandTimeout = 600; return(sqlCmd.ExecuteNonQuery()); } catch (Exception) { throw; } finally { closeConn(sqlConn); } }
/// <summary> /// 用户注册或登陆后获取用户某个字段信息 </summary> /// <param name="ComText">SQL语句</param> /// <returns>根据sql语句得到的字段信息</returns> public static string GetFieldValue(string ComText) { SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); SqlCommand cmd = new SqlCommand(ComText, sqlConn); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); openConn(sqlConn); sda.Fill(ds, "table"); closeConn(sqlConn); string i; if (ds.Tables[0].Rows.Count > 0) { i = (ds.Tables[0].Rows[0][0].ToString()); } else { i = ""; } return(i); }
/// <summary> /// 使用sql语句获取SqlDataReader的参考代码 /// </summary> /// <param name="MySQL"></param> /// <returns></returns> public SqlDataReader GetSqlReader(String MySQL) { //// 数据库连接参数(对客户端应用程序配置文件的访问) //string strConn = @ConfigurationManager.ConnectionStrings["connectionStrings"].ConnectionString; //// 创建连接数据库的一个打开连接 //SqlConnection MyConn = new SqlConnection(strConn); SqlConnection MyConn = new SqlConnection(); //SqlCommand sqlCmd = new SqlCommand(); MyConn.ConnectionString = DBCallCommon.GetStringValue("connectionStrings"); try // 正常运行 { // 使用 ConnectionString 所指定的属性设置打开数据库连接 MyConn.Open(); // 数据库SQL语句 String strSQL = @MySQL.Trim().ToString(); // 要对数据库执行的一个SQL语句或存储过程 SqlCommand MyComm = new SqlCommand(strSQL, MyConn); // 提供一种从数据库读取只进的行流的一种方式 SqlDataReader MyReader = MyComm.ExecuteReader(); // 读取数据,判断是否有数据 if (MyReader.HasRows) { // 返回成功 return(MyReader); } else { Console.Write("<script language=JavaScript>"); Console.Write("alert('系统提示:数据读取失败或网络忙,请稍后再试!');"); Console.Write("</script>"); if ((MyReader != null) & (MyReader.IsClosed != true)) { // 关闭 MyReader.Close(); } // 判断数据库连接 if (MyConn.State == ConnectionState.Open) { // 关闭数据库连接 MyConn.Close(); } // 返回失败 return(null); } } catch (SqlException) // 数据库操作异常处理 { Console.Write("<script language=JavaScript>"); Console.Write("alert('系统提示:当前数据库操作失败或网络忙,请稍后再试!');"); Console.Write("</script>"); if (MyConn.State == ConnectionState.Open) { // 关闭数据库连接 MyConn.Close(); } // 返回失败 return(null); } catch // 异常处理 { if (MyConn.State == ConnectionState.Open) { // 关闭数据库连接 MyConn.Close(); } // 返回失败 return(null); } finally // 执行完毕清除在try块中分配的任何资源 { if (MyConn.State == ConnectionState.Open) { // 关闭数据库连接 MyConn.Close(); } } }