/// <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();
}
}
}