You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
2193 lines
92 KiB
2193 lines
92 KiB
using NFine.Code;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Data.SqlClient;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Net;
|
|
using System.Security.Cryptography;
|
|
using System.Text;
|
|
using System.Text.RegularExpressions;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NFine.Data.Extensions
|
|
{
|
|
/// <summary>
|
|
/// 数据库访问辅助类，add by ngye, on 2013-08-14.
|
|
/// </summary>
|
|
public static class SqlHelper
|
|
{
|
|
#region [ 连接串相关 ]
|
|
/// <summary>
|
|
/// 数据中心DB的连接字符串
|
|
/// </summary>
|
|
public static string DataCenterConnString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
|
|
public static string DataCenterU8ConnString = ConfigurationManager.ConnectionStrings["U8connstr"].ConnectionString;
|
|
/// <summary>
|
|
/// 获取同步服务器的连接
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static SqlConnection GetDataCenterConn()
|
|
{
|
|
return new SqlConnection(DataCenterConnString);
|
|
}
|
|
/// <summary>
|
|
/// 根据连接串获取连接
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static SqlConnection GetConnByStr(string connStr)
|
|
{
|
|
string ConnString = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
|
|
return new SqlConnection(ConnString);
|
|
}
|
|
/// <summary>
|
|
/// MD5解密
|
|
/// </summary>
|
|
/// <param name="str"></param>
|
|
/// <returns></returns>
|
|
public static string FromMd5(string str)
|
|
{
|
|
//return str;
|
|
return Decrypt(str, "&%#@?,:*_");
|
|
}
|
|
/// <summary>
|
|
/// 解密
|
|
/// </summary>
|
|
/// <param name="strText"></param>
|
|
/// <param name="sDecrKey"></param>
|
|
/// <returns></returns>
|
|
private static String Decrypt(String strText, String sDecrKey)
|
|
{
|
|
Byte[] byKey = { };
|
|
Byte[] IV = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
|
|
Byte[] inputByteArray = new byte[strText.Length];
|
|
try
|
|
{
|
|
byKey = System.Text.Encoding.UTF8.GetBytes(sDecrKey.Substring(0, 8));
|
|
DESCryptoServiceProvider des = new DESCryptoServiceProvider();
|
|
inputByteArray = Convert.FromBase64String(strText);
|
|
MemoryStream ms = new MemoryStream();
|
|
CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(byKey, IV),
|
|
CryptoStreamMode.Write);
|
|
cs.Write(inputByteArray, 0, inputByteArray.Length);
|
|
cs.FlushFinalBlock();
|
|
System.Text.Encoding encoding = System.Text.Encoding.UTF8;
|
|
return encoding.GetString(ms.ToArray());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ex.Message;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 根据连接串获取连接
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public static SqlConnection GetConnByString(string conn)
|
|
{
|
|
return new SqlConnection(conn);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 测试连接串是否能正确
|
|
/// </summary>
|
|
/// <param name="connectionString">连接串</param>
|
|
/// <returns></returns>
|
|
public static bool TestConnectionString(string connectionString)
|
|
{
|
|
bool result = true;
|
|
try
|
|
{
|
|
using (SqlConnection conn = GetConnByString(connectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result = false;
|
|
}
|
|
}
|
|
}
|
|
catch (Exception)
|
|
{
|
|
result = false;
|
|
}
|
|
return result;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 测试连接串是否正确
|
|
/// </summary>
|
|
/// <param name="connectionString">连接串</param>
|
|
/// <param name="timeOut">测试的超时秒数</param>
|
|
/// <param name="errInfo">错误时输出的错误信息</param>
|
|
/// <returns>是否能正常连接</returns>
|
|
public static bool TestConnectionString(string connectionString, int timeOut, ref string errInfo)
|
|
{
|
|
bool result = true;
|
|
string[] arr = connectionString.Split(new char[] { ';' });
|
|
List<string> list = new List<string>();
|
|
|
|
foreach (string s in arr)
|
|
{
|
|
if (s.ToLower().IndexOf("timeout") == -1)
|
|
{
|
|
list.Add(s);
|
|
}
|
|
else
|
|
{
|
|
list.Add(String.Format("Connection Timeout={0}", timeOut));
|
|
}
|
|
}
|
|
|
|
SqlConnection con = null;
|
|
|
|
try
|
|
{
|
|
con = new SqlConnection(string.Join(";", list.ToArray()));
|
|
con.Open();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result = false;
|
|
errInfo = ex.Message;
|
|
}
|
|
finally
|
|
{
|
|
if (con != null)
|
|
{
|
|
con.Close();
|
|
con.Dispose();
|
|
con = null;
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
#region [ 超时设置 ]
|
|
public static int CommandTimeout
|
|
{
|
|
get
|
|
{
|
|
return 7200;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
public static string HttpPost(string url, string body)
|
|
{
|
|
try
|
|
{
|
|
Encoding encoding = Encoding.UTF8;
|
|
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
|
|
request.Method = "POST";
|
|
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
|
|
request.ContentType = "application/json; charset=utf-8";
|
|
|
|
byte[] buffer = encoding.GetBytes(body);
|
|
request.ContentLength = buffer.Length;
|
|
request.GetRequestStream().Write(buffer, 0, buffer.Length);
|
|
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
|
using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
|
|
{
|
|
return reader.ReadToEnd();
|
|
}
|
|
}
|
|
catch (WebException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// Http Get请求
|
|
/// </summary>
|
|
/// <param name="url"></param>
|
|
/// <param name="headerValue"></param>
|
|
/// <returns></returns>
|
|
public static String httpGet(string url)
|
|
{
|
|
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
|
|
|
|
WebHeaderCollection headers = new WebHeaderCollection();
|
|
//headers.Add("Token", headerValue[0]);
|
|
//headers.Add("Timespan", headerValue[1]);
|
|
request.UserAgent = null;
|
|
request.Headers = headers;
|
|
request.Method = "GET";
|
|
|
|
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
|
var httpStatusCode = (int)response.StatusCode;
|
|
Console.WriteLine("返回码为 {0}", httpStatusCode);
|
|
if (httpStatusCode == 200)
|
|
{
|
|
Stream myResponseStream = response.GetResponseStream();
|
|
StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
|
|
string retString = myStreamReader.ReadToEnd();
|
|
myStreamReader.Close();
|
|
myResponseStream.Close();
|
|
return retString;
|
|
}
|
|
else
|
|
{
|
|
Console.WriteLine("未返回数据 {0}", httpStatusCode);
|
|
throw new Exception("no data response");
|
|
}
|
|
|
|
}
|
|
//有header头使用
|
|
public static String httpGetByHeader(string url, string HeaderName, string HeaderValue)
|
|
{
|
|
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
|
|
|
|
WebHeaderCollection headers = new WebHeaderCollection();
|
|
headers.Add(HeaderName, HeaderValue);
|
|
//headers.Add("Timespan", headerValue[1]);
|
|
request.UserAgent = null;
|
|
request.Headers = headers;
|
|
request.Method = "GET";
|
|
|
|
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
|
var httpStatusCode = (int)response.StatusCode;
|
|
Console.WriteLine("返回码为 {0}", httpStatusCode);
|
|
if (httpStatusCode == 200)
|
|
{
|
|
Stream myResponseStream = response.GetResponseStream();
|
|
StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
|
|
string retString = myStreamReader.ReadToEnd();
|
|
myStreamReader.Close();
|
|
myResponseStream.Close();
|
|
return retString;
|
|
}
|
|
else
|
|
{
|
|
Console.WriteLine("未返回数据 {0}", httpStatusCode);
|
|
throw new Exception("no data response");
|
|
}
|
|
|
|
}
|
|
|
|
#region [ ExecuteNonQuery ]
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回受影响行数
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>受影响行数</returns>
|
|
public static int ExecuteNonQuery(string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
|
|
|
|
//多语句一起事务中执行(Li编写)
|
|
public static int CmdExecuteNonQueryLi(string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlTransaction sqlTran = conn.BeginTransaction();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.Transaction = sqlTran;
|
|
cmd.Connection = conn;
|
|
try
|
|
{
|
|
cmd.CommandText = sql;
|
|
int RES = cmd.ExecuteNonQuery();
|
|
cmd.Transaction.Commit();
|
|
return RES;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
cmd.Transaction.Rollback();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State != ConnectionState.Closed)
|
|
{
|
|
conn.Close();
|
|
conn.Dispose();
|
|
}
|
|
}
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
public static int ExecuteNonQuery(string connStr, string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = new SqlConnection(connStr))
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region [ ExecuteScalar_ForProc ]
|
|
/// <summary>
|
|
/// 根据存储过程和参数，返回Scalar结果
|
|
/// </summary>
|
|
/// <param name="proc">存储过程</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>object</returns>
|
|
public static object ExecuteScalar_ForProc(string proc, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(proc, conn).AddTimeout();
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
object obj = cmd.ExecuteScalar();
|
|
return obj;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据存储过程和参数，返回Scalar结果，但不加上超时设置 ( 避免循环做 AddTimeout )
|
|
/// </summary>
|
|
/// <param name="proc">存储过程</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>object</returns>
|
|
public static object ExecuteScalar_ForProc_WithoutAddTimeout(string proc, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(proc, conn);
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
object obj = cmd.ExecuteScalar();
|
|
return obj;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region [ ExecuteScalar ]
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回Scalar结果
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>object</returns>
|
|
public static object ExecuteScalar(string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
object obj = cmd.ExecuteScalar();
|
|
return obj;
|
|
}
|
|
}
|
|
#endregion
|
|
/// <summary>
|
|
/// 获取数据字典公共方法
|
|
/// </summary>
|
|
/// <param name="F_EnCode"></param>
|
|
/// <returns></returns>
|
|
public static string GetItemsDetails(string F_EnCode)
|
|
{
|
|
string sql = @"SELECT a.F_ItemCode,a.F_ItemName FROM Sys_SRM_ItemsDetail a
|
|
LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
|
|
WHERE b.F_EnCode = '{0}'";
|
|
sql = string.Format(sql, F_EnCode);
|
|
DataTable dt = GetDataTableBySql(sql);
|
|
return dt.Rows[0]["F_ItemCode"].ToString();
|
|
|
|
}
|
|
|
|
#region [ QueryByProc ]
|
|
/// <summary>
|
|
/// 根据存储过程及参数，返回DataSet
|
|
/// </summary>
|
|
/// <returns>DataSet</returns>
|
|
public static DataSet GetDataSetByProc(string proc, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(proc, conn).AddTimeout();
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据存储过程及参数，返回DataTable
|
|
/// </summary>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableByProc(string proc, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(proc, conn).AddTimeout();
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
DataTable dt = cmd.ExecuteDataTable();
|
|
return dt;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataRow
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataRow</returns>
|
|
public static DataRow GetDataRowByProc(string proc, params SqlParameter[] spArr)
|
|
{
|
|
DataTable dt = GetDataTableByProc(proc, spArr);
|
|
if (dt == null || dt.Rows.Count == 0)
|
|
return null;
|
|
|
|
return dt.Rows[0];
|
|
}
|
|
#endregion
|
|
|
|
#region [ Query ]
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataSet
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataSet</returns>
|
|
public static DataSet GetDataSetBySql(string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataTable
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableBySql(string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
DataTable dt = cmd.ExecuteDataTable();
|
|
return dt;
|
|
}
|
|
}
|
|
public static string GetSerialCodeBYVenDor(string workPointCode, string tbName, string colName, string Pre, int numLen)
|
|
{
|
|
string sql = "EXEC Addins_GetSerialCode '','{0}','{1}','{2}',{3}";
|
|
sql = string.Format(sql, new object[] { tbName, colName, Pre, numLen });
|
|
return DbHelper.ExecuteScalarByVenDor(CommandType.Text, sql).ToString();
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataRow
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataRow</returns>
|
|
public static DataRow GetDataRowBySql(string sql, params SqlParameter[] spArr)
|
|
{
|
|
DataTable dt = GetDataTableBySql(sql, spArr);
|
|
if (dt == null || dt.Rows.Count == 0)
|
|
return null;
|
|
|
|
return dt.Rows[0];
|
|
}
|
|
#endregion
|
|
|
|
#region [ GetValue ]
|
|
public static object GetValue(DataRow dr, string fieldName, object replaceValue)
|
|
{
|
|
if (dr.IsNull(fieldName))
|
|
return replaceValue;
|
|
return dr[fieldName];
|
|
}
|
|
|
|
public static object GetValue(DataRow dr, int idx, object replaceValue)
|
|
{
|
|
if (dr.IsNull(idx))
|
|
return replaceValue;
|
|
return dr[idx];
|
|
}
|
|
#endregion
|
|
|
|
#region [ GetString ]
|
|
public static string GetString(DataRow dr, string fieldName, string replaceValue)
|
|
{
|
|
if (dr.IsNull(fieldName))
|
|
return replaceValue;
|
|
return Convert.ToString(dr[fieldName]);
|
|
}
|
|
|
|
public static string GetString(DataRow dr, int idx, string replaceValue)
|
|
{
|
|
if (dr.IsNull(idx))
|
|
return replaceValue;
|
|
return Convert.ToString(dr[idx]);
|
|
}
|
|
#endregion
|
|
|
|
#region [ GetDateTime ]
|
|
public static DateTime GetDateTime(DataRow dr, string fieldName, DateTime replaceValue)
|
|
{
|
|
if (dr.IsNull(fieldName))
|
|
return replaceValue;
|
|
return Convert.ToDateTime(dr[fieldName]);
|
|
}
|
|
public static DateTime GetDateTime(DataRow dr, int idx, DateTime replaceValue)
|
|
{
|
|
if (dr.IsNull(idx))
|
|
return replaceValue;
|
|
return Convert.ToDateTime(dr[idx]);
|
|
}
|
|
#endregion
|
|
|
|
#region [ 非数据中心库操作 ]
|
|
|
|
#region [ ExecuteScalar ]
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回Scalar结果
|
|
/// </summary>
|
|
/// <param name="connString">连接串</param>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>object</returns>
|
|
public static object ExecuteScalarWithConn(string connString, string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetConnByString(connString))
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
object obj = cmd.ExecuteScalar();
|
|
return obj;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region [ ExecuteNonQuery ]
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回受影响行数
|
|
/// </summary>
|
|
/// <param name="connString">连接串</param>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>受影响行数</returns>
|
|
public static int ExecuteNonQueryWithConn(string connString, string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetConnByString(connString))
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region [ Query ]
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataTable
|
|
/// </summary>
|
|
/// <param name="connString">连接串</param>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableBySqlWithConn(string connString, string sql, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetConnByString(connString))
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
DataTable dt = cmd.ExecuteDataTable();
|
|
return dt;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataRow
|
|
/// </summary>
|
|
/// <param name="connString">连接串</param>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataRow</returns>
|
|
public static DataRow GetDataRowBySqlWithConn(string connString, string sql, params SqlParameter[] spArr)
|
|
{
|
|
DataTable dt = GetDataTableBySqlWithConn(connString, sql, spArr);
|
|
if (dt == null || dt.Rows.Count == 0)
|
|
return null;
|
|
|
|
return dt.Rows[0];
|
|
}
|
|
#endregion
|
|
#endregion
|
|
|
|
#region [ 根据SQL文件路径执行 ]
|
|
/// <summary>
|
|
/// 执行SQL文件
|
|
/// </summary>
|
|
/// <param name="connString"></param>
|
|
/// <param name="filePath"></param>
|
|
/// <returns></returns>
|
|
public static bool ExecuteNonQueryWithConnAndSqlFilePath(string connString, string filePath)
|
|
{
|
|
string sql = System.IO.File.ReadAllText(filePath);
|
|
return ExecuteNonQueryWithConnAndGO(connString, sql);
|
|
}
|
|
#endregion
|
|
|
|
#region [ 执行带Go语句 ]
|
|
/// <summary>
|
|
/// 执行带"GO"的SQL，返回最后一条SQL的受影响行数
|
|
/// </summary>
|
|
/// <param name="connString">连接串</param>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <returns>返回最后一条SQL的受影响行数</returns>
|
|
public static bool ExecuteNonQueryWithConnAndGO(string connString, string sql)
|
|
{
|
|
bool result = true;
|
|
string[] arr = System.Text.RegularExpressions.Regex.Split(sql, @"\bGO\b", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
|
|
using (SqlConnection conn = GetConnByString(connString))
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand().AddTimeout();
|
|
cmd.Connection = conn;
|
|
SqlTransaction tx = conn.BeginTransaction();
|
|
cmd.Transaction = tx;
|
|
try
|
|
{
|
|
for (int n = 0; n < arr.Length; n++)
|
|
{
|
|
string strsql = arr[n];
|
|
if (strsql.Trim().Length > 1 && strsql.Trim().Replace(";", "") != "")
|
|
{
|
|
cmd.CommandText = strsql;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
tx.Commit();
|
|
}
|
|
catch (System.Data.SqlClient.SqlException E)
|
|
{
|
|
tx.Rollback();
|
|
result = false;
|
|
//return -1;
|
|
throw new Exception(E.Message);
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State != ConnectionState.Closed)
|
|
{
|
|
conn.Close();
|
|
conn.Dispose();
|
|
}
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
#endregion
|
|
|
|
|
|
/// <summary>
|
|
/// 设置Sql参数
|
|
/// </summary>
|
|
/// <param name="parameterName"></param>
|
|
/// <param name="value"></param>
|
|
/// <param name="dbType"></param>
|
|
/// <returns></returns>
|
|
public static SqlParameter SetSqlParameter(string parameterName, object value, SqlDbType dbType)
|
|
{
|
|
if (value == null || string.IsNullOrEmpty(value.ToString()))
|
|
{
|
|
return new SqlParameter(parameterName, DBNull.Value);
|
|
}
|
|
SqlParameter sp = new SqlParameter(parameterName, value);
|
|
sp.SqlDbType = dbType;
|
|
return sp;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql及带 in 的id参数列表, 得到DataTable
|
|
/// </summary>
|
|
/// <param name="sql">带in ( {0} )的sql</param>
|
|
/// <param name="ids">以逗号分隔的id字符串</param>
|
|
/// <param name="idDbType">id的Sql数据类型</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableWithIn(string sql, string ids, SqlDbType idDbType)
|
|
{
|
|
SqlParameter[] spArr = GetWithInSqlParameters(ref sql, ids, idDbType);
|
|
return GetDataTableBySql(sql, spArr);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql及带 in 的id参数列表, 得到受影响行数
|
|
/// </summary>
|
|
/// <param name="sql">带in ( {0} )的sql</param>
|
|
/// <param name="ids">以逗号分隔的id字符串</param>
|
|
/// <param name="idDbType">id的Sql数据类型</param>
|
|
/// <returns>DataTable</returns>
|
|
public static int ExecuteNonQueryWithIn(string sql, string ids, SqlDbType idDbType)
|
|
{
|
|
SqlParameter[] spArr = GetWithInSqlParameters(ref sql, ids, idDbType);
|
|
return ExecuteNonQuery(sql, spArr);
|
|
}
|
|
|
|
#region [ 带 in 不确定参数的执行方法 ]
|
|
/// <summary>
|
|
/// 获取带 in 的sql参数列表
|
|
/// </summary>
|
|
/// <param name="sql">带in ( {0} )的sql</param>
|
|
/// <param name="ids">以逗号分隔的id字符串</param>
|
|
/// <param name="idDbType">id的Sql数据类型</param>
|
|
/// <returns>sql参数列表</returns>
|
|
public static SqlParameter[] GetWithInSqlParameters(ref string sql, string ids, SqlDbType idDbType)
|
|
{
|
|
if (string.IsNullOrEmpty(ids))
|
|
{
|
|
return null;
|
|
}
|
|
|
|
string[] idArr = ids.Split(',');
|
|
//组建sql在in中的字符串
|
|
StringBuilder sbCondition = new StringBuilder();
|
|
List<SqlParameter> spList = new List<SqlParameter>();
|
|
for (int i = 0; i < idArr.Length; i++)
|
|
{
|
|
string id = idArr[i];
|
|
string spName = string.Format("@id{0}", i);
|
|
sbCondition.AppendFormat("{0},", spName);
|
|
spList.Add(SetSqlParameter(spName, id, idDbType));
|
|
}
|
|
//重新构建sql
|
|
sql = string.Format(sql, sbCondition.ToString().TrimEnd(','));
|
|
return spList.ToArray();
|
|
}
|
|
#endregion
|
|
|
|
public static string Organize_F_ParentId(string F_Account)
|
|
{
|
|
string F_ParentId = string.Empty;
|
|
string sql = @"
|
|
SELECT c.F_ID,c.F_ParentId FROM sys_srm_user a
|
|
LEFT JOIN sys_SRM_Role b ON a.F_RoleID=b.F_ID
|
|
LEFT JOIN Sys_SRM_Organize c ON b.F_OrganizeId=c.F_ID
|
|
WHERE a.F_Account='{0}'";
|
|
sql = string.Format(sql, F_Account);
|
|
DataTable dt = GetDataTableBySql(sql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
if (dt.Rows[0]["F_ParentId"].ToString() == "0")
|
|
{
|
|
F_ParentId = "0";
|
|
}
|
|
else
|
|
{
|
|
F_ParentId = dt.Rows[0]["F_ID"].ToString();
|
|
}
|
|
|
|
}
|
|
return F_ParentId;
|
|
}
|
|
//public static string OrganizeByVendor_F_ParentId(string sqls,string F_Account)
|
|
//{
|
|
// string F_ParentId = string.Empty;
|
|
// string sql = @"
|
|
// SELECT b.F_EnCode FROM (SELECT* FROM Sys_SRM_OrganizeByVendor a
|
|
// WHERE a.F_EnCode= '{0}')aa
|
|
// LEFT JOIN Sys_SRM_OrganizeByVendor b ON aa.F_ID=b.F_ParentID";
|
|
// sql = string.Format(sql, F_Account);
|
|
// DataTable dt = GetDataTableBySql(sql);
|
|
// if (dt.Rows.Count > 0)
|
|
// {
|
|
// if (string.IsNullOrWhiteSpace( dt.Rows[0]["F_EnCode"].ToString()))
|
|
// {
|
|
// F_ParentId = "0";
|
|
// }
|
|
// else
|
|
// {
|
|
// sqls += "and vencode IN (" + sql + ")";
|
|
// }
|
|
|
|
// }
|
|
// return sqls;
|
|
//}
|
|
|
|
public static string OrganizeByVendor_F_ParentId(string sqls, string F_Account)
|
|
{
|
|
string F_ParentId = string.Empty;
|
|
string purchugcode = string.Empty;
|
|
string sql = @"
|
|
SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
|
|
LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
|
|
WHERE a.F_EnCode='Vendor_Category' AND b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
|
|
sql = string.Format(sql, F_Account);
|
|
DataTable dt = GetDataTableBySql(sql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
|
|
}
|
|
sqls += @"and vencode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";
|
|
|
|
}
|
|
//else
|
|
//{
|
|
// sqls += @"and vencode = '" + purchugcode.TrimEnd(',') + "'";
|
|
//}
|
|
return sqls;
|
|
}
|
|
|
|
|
|
public static string OrganizeByVendor_F_ParentIdByCommint(string sqls, string F_Account)
|
|
{
|
|
string F_ParentId = string.Empty;
|
|
string purchugcode = string.Empty;
|
|
string sql = @"
|
|
SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
|
|
LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
|
|
WHERE a.F_EnCode='Vendor_Category' AND b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
|
|
sql = string.Format(sql, F_Account);
|
|
DataTable dt = GetDataTableBySql(sql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
|
|
}
|
|
sqls += @"and a.vencode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";
|
|
|
|
}
|
|
sqls += " GROUP BY POCode,CreateDateTime,e.ProjectCode,a.VenCode,d.VenName,ReleaseState,CreatePerson,a.Filename,a.uploadcomment ,a.SignBackStatus,a.WorkPoint ,a.EATTRIBUTE7,a.EATTRIBUTE8, a.EATTRIBUTE9,a.EATTRIBUTE10 ,a.EATTRIBUTE11 ,a.EATTRIBUTE12,a.BackComment";
|
|
return sqls;
|
|
}
|
|
|
|
public static string OrganizeByVendor_F_ParentIdBYJX(string sqls, string F_Account)
|
|
{
|
|
|
|
string F_ParentId = string.Empty;
|
|
string purchugcode = string.Empty;
|
|
string sql = @"
|
|
SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
|
|
LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
|
|
WHERE a.F_EnCode='Vendor_Category' AND b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
|
|
sql = string.Format(sql, F_Account);
|
|
DataTable dt = GetDataTableBySql(sql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
|
|
}
|
|
sqls += @"and a.vencode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";
|
|
|
|
}
|
|
return sqls;
|
|
|
|
|
|
//string F_ParentId = string.Empty;
|
|
//string sql = @"
|
|
//SELECT b.F_EnCode FROM (SELECT* FROM Sys_SRM_OrganizeByVendor a
|
|
//WHERE a.F_EnCode= '{0}')aa
|
|
//LEFT JOIN Sys_SRM_OrganizeByVendor b ON aa.F_ID=b.F_ParentID";
|
|
//sql = string.Format(sql, F_Account);
|
|
//DataTable dt = GetDataTableBySql(sql);
|
|
//if (dt.Rows.Count > 0)
|
|
//{
|
|
// if (string.IsNullOrWhiteSpace(dt.Rows[0]["F_EnCode"].ToString()))
|
|
// {
|
|
// F_ParentId = "0";
|
|
// }
|
|
// else
|
|
// {
|
|
// sqls += "and a.vencode IN (" + sql + ")";
|
|
// }
|
|
|
|
//}
|
|
//return sqls;
|
|
}
|
|
|
|
public static string OrganizeByVendor_F_ParentIdBYDZ(string sqls, string F_Account)
|
|
{
|
|
string F_ParentId = string.Empty;
|
|
string purchugcode = string.Empty;
|
|
string sql = @"
|
|
SELECT b.F_ItemCode as purchugcode,b.F_ItemName as purchugname from [dbo].[Sys_SRM_Items] a
|
|
LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
|
|
WHERE a.F_EnCode='Vendor_Category' AND b.F_EnabledMark='1' AND b.F_ItemName LIKE '%{0}%'";
|
|
sql = string.Format(sql, F_Account);
|
|
DataTable dt = GetDataTableBySql(sql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
purchugcode += "'" + dt.Rows[i]["purchugcode"].ToString() + "',";
|
|
}
|
|
sqls += @"and SupplierCode IN ( SELECT vencode FROM dbo.ICSVendor WHERE VCCode IN(" + purchugcode.TrimEnd(',') + "))";
|
|
|
|
}
|
|
return sqls;
|
|
|
|
|
|
|
|
//string F_ParentId = string.Empty;
|
|
//string sql = @"
|
|
//SELECT b.F_EnCode FROM (SELECT* FROM Sys_SRM_OrganizeByVendor a
|
|
//WHERE a.F_EnCode= '{0}')aa
|
|
//LEFT JOIN Sys_SRM_OrganizeByVendor b ON aa.F_ID=b.F_ParentID";
|
|
//sql = string.Format(sql, F_Account);
|
|
//DataTable dt = GetDataTableBySql(sql);
|
|
//if (dt.Rows.Count > 0)
|
|
//{
|
|
// if (string.IsNullOrWhiteSpace(dt.Rows[0]["F_EnCode"].ToString()))
|
|
// {
|
|
// F_ParentId = "0";
|
|
// }
|
|
// else
|
|
// {
|
|
// sqls += "and SupplierCode IN (" + sql + ")";
|
|
// }
|
|
|
|
//}
|
|
//return sqls;
|
|
}
|
|
|
|
|
|
|
|
public static string Organize_F_Account(string F_ParentId, string Selectsql)
|
|
{
|
|
DataTable dt = null;
|
|
string sql = @"with
|
|
CTE
|
|
as
|
|
(
|
|
select * from Sys_SRM_Organize where F_Id='{0}'
|
|
union all
|
|
select G.* from CTE inner join Sys_SRM_Organize as G
|
|
on CTE.F_Id=G.F_ParentID
|
|
){1}
|
|
AND CreatePerson IN
|
|
|
|
(select F_RealName from CTE a
|
|
inner JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
|
|
inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
|
|
)";
|
|
sql = string.Format(sql, F_ParentId, Selectsql);
|
|
return sql;
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTemp(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTemp(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
//订单回签
|
|
|
|
public static DataTable FindTablePageBySql_OtherTempByCommint(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempByCommint(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
public static DataTable FindTablePageBySql_OtherTempByVendor(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempByVendor(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTemp2(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTemp2(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTempKbs(ref Pagination Pagination, DataTable dts)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempKbs(pageIndex, pageSize, ref totalRow, dts);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTemp3(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTemp3(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTemp4(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTemp4(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
|
|
private static DataTable GetPageTable_EXISTSTemp(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by " + orderField + " " + orderType + " ";
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql = @" with
|
|
CTE
|
|
as
|
|
(
|
|
select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
|
|
union all
|
|
select G.* from CTE inner join Sys_SRM_Organize as G
|
|
on CTE.F_Id=G.F_ParentID
|
|
) Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += @" As rowNum, * From (" + sqlTempName + @" AND PersonCode IN
|
|
|
|
(select F_Account from CTE a
|
|
inner JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
|
|
inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
|
|
)) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
//string ss = ds.Tables[0].Rows[0][0].ToString();
|
|
count = ds.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
|
|
private static DataTable GetPageTable_EXISTSTempByCommint(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by " + orderField + " " + orderType + " ";
|
|
string sqlcount = string.Empty;
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql = @" with
|
|
CTE
|
|
as
|
|
(
|
|
select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
|
|
union all
|
|
select G.* from CTE inner join Sys_SRM_Organize as G
|
|
on CTE.F_Id=G.F_ParentID
|
|
) Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += @" As rowNum, * From (" + sqlTempName + @" AND PersonCode IN
|
|
|
|
(select F_Account from CTE a
|
|
inner JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
|
|
inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
|
|
) GROUP BY POCode,CreateDateTime,e.ProjectCode,a.VenCode,d.VenName,ReleaseState,CreatePerson,a.Filename,a.uploadcomment ,a.SignBackStatus,a.WorkPoint ,a.EATTRIBUTE7,a.EATTRIBUTE8, a.EATTRIBUTE9,a.EATTRIBUTE10 ,a.EATTRIBUTE11 ,a.EATTRIBUTE12,a.BackComment) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
|
|
sqlcount += @"with
|
|
CTE
|
|
as
|
|
(
|
|
select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
|
|
union all
|
|
select G.* from CTE inner join Sys_SRM_Organize as G
|
|
on CTE.F_Id=G.F_ParentID
|
|
) Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")";
|
|
sqlcount += @" As rowNum, * From (" + sqlTempName + @" AND PersonCode IN
|
|
|
|
(select F_Account from CTE a
|
|
inner JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
|
|
inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
|
|
) GROUP BY POCode,CreateDateTime,e.ProjectCode,a.VenCode,d.VenName,ReleaseState,CreatePerson,a.Filename,a.uploadcomment ,a.SignBackStatus,a.WorkPoint ,a.EATTRIBUTE7,a.EATTRIBUTE8, a.EATTRIBUTE9,a.EATTRIBUTE10 ,a.EATTRIBUTE11 ,a.EATTRIBUTE12,a.BackComment) As T ) As N";
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
SqlCommand cmdcount = new SqlCommand(sqlcount, conn).AddTimeout();
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
|
|
SqlDataAdapter adaptercount = new SqlDataAdapter(cmdcount);
|
|
DataSet dscount = new DataSet();
|
|
adaptercount.Fill(dscount);
|
|
//string ss = ds.Tables[0].Rows[0][0].ToString();
|
|
count = dscount.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(dscount.Tables[0].Rows.Count);
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
|
|
private static DataTable GetPageTable_EXISTSTempByVendor(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by " + orderField + " " + orderType + " ";
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql = @" with
|
|
CTE
|
|
as
|
|
(
|
|
select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
|
|
union all
|
|
select G.* from CTE inner join Sys_SRM_Organize as G
|
|
on CTE.F_Id=G.F_ParentID
|
|
) Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += @" As rowNum, * From (" + sqlTempName + @" AND PersonCode IN
|
|
|
|
(select F_Account from CTE a
|
|
)) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
//string ss = ds.Tables[0].Rows[0][0].ToString();
|
|
count = ds.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
//招投标权限
|
|
|
|
|
|
private static DataTable GetPageTable_EXISTSTemp2(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by InvCode desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[1].Rows[0][0].ToString();
|
|
count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
|
|
return ds.Tables[1];
|
|
}
|
|
}
|
|
|
|
|
|
|
|
private static DataTable GetPageTable_EXISTSTempKbs(int pageIndex, int pageSize, ref int count, DataTable dts)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
//string OrderBy = " order by InvCode desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
//conn.Open();
|
|
//sql += " Select Count(1) From " + sqlTempName;
|
|
//sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
//sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
//sql += Droptable;
|
|
//SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
//SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
//DataSet ds = new DataSet();
|
|
//adapter.Fill(ds);
|
|
//string ss = dts.Rows[0][0].ToString();
|
|
//count = Convert.ToInt32(dts.Rows.Count.ToString());
|
|
//return dts;
|
|
|
|
DataTable resultTable = dts.Clone();
|
|
for (int i = num; i < Math.Min(num1, dts.Rows.Count); i++)
|
|
{
|
|
resultTable.ImportRow(dts.Rows[i]);
|
|
}
|
|
|
|
// 计算总记录数
|
|
count = dts.Rows.Count;
|
|
return resultTable;
|
|
|
|
|
|
}
|
|
}
|
|
|
|
|
|
private static DataTable GetPageTable_EXISTSTemp3(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by VENDORCODE desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[0].Rows[0][0].ToString();
|
|
if (ds.Tables[1].Rows.Count > 0)
|
|
{
|
|
count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
else
|
|
{
|
|
return new DataTable();
|
|
}
|
|
}
|
|
}
|
|
|
|
private static DataTable GetPageTable_EXISTSTemp4(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
//string OrderBy = " order by dDate desc ";
|
|
string OrderBy = " order by " + orderField + " " + orderType + " ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[0].Rows[0][0].ToString();
|
|
if (ds.Tables[1].Rows.Count > 0)
|
|
{
|
|
count = Convert.ToInt32(ss);
|
|
return ds.Tables[1];
|
|
}
|
|
else
|
|
{
|
|
return new DataTable();
|
|
}
|
|
}
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTempbyLOGUSER(string strSql, string TempName, string ParentId, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempbyLOGUSER(strSql, TempName, ParentId, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
private static DataTable GetPageTable_EXISTSTempbyLOGUSER(string sql, string sqlTempName, string ParentId, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by " + orderField + " " + orderType + " ";
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql = @" with
|
|
CTE
|
|
as
|
|
(
|
|
select * from Sys_SRM_Organize where F_Id='" + ParentId + @"'
|
|
union all
|
|
select G.* from CTE inner join Sys_SRM_Organize as G
|
|
on CTE.F_Id=G.F_ParentID
|
|
) Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += @" As rowNum, * From (" + sqlTempName + @" AND A.LOGUSER IN
|
|
|
|
(select F_RealName from CTE a
|
|
inner JOIN sys_SRM_Role b ON a.F_ID=b.F_OrganizeId
|
|
inner JOIN sys_srm_user c ON b.F_ID=c.F_RoleID
|
|
)) As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
//string ss = ds.Tables[0].Rows[0][0].ToString();
|
|
count = ds.Tables[0].Rows.Count <= 0 ? 0 : Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
|
|
|
|
public static DataTable FindTablePageBySql_OtherTempByZTB(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempByZTB(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
private static DataTable GetPageTable_EXISTSTempByZTB(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by InvCode desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[1].Rows[0][0].ToString();
|
|
count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
public static DataTable FindTablePageBySql_OtherTempSORRFQbyChan(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempSORRFQbyChan(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
private static DataTable GetPageTable_EXISTSTempSORRFQbyChan(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by 物料编码 desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[1].Rows[0][0].ToString();
|
|
count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
public static DataTable FindTablePageBySql_OtherTempSORRFQ(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTempSORRFQ(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
private static DataTable GetPageTable_EXISTSTempSORRFQ(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by ITEMCODE desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[1].Rows[0][0].ToString();
|
|
count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
|
|
public static DataTable FindTablePageBySql_OtherTemps(string strSql, string TempName, string Droptable, DbParameter[] parameters, ref Pagination Pagination)
|
|
{
|
|
string orderField = Pagination.sidx;
|
|
string orderType = Pagination.sord;
|
|
int pageIndex = Pagination.page;
|
|
int pageSize = Pagination.rows;
|
|
int totalRow = Pagination.records;
|
|
DataTable dt = GetPageTable_EXISTSTemps(strSql, TempName, Droptable, parameters, orderField, orderType, pageIndex, pageSize, ref totalRow);
|
|
Pagination.records = totalRow;
|
|
return dt;
|
|
}
|
|
|
|
private static DataTable GetPageTable_EXISTSTemps(string sql, string sqlTempName, string Droptable, DbParameter[] param, string orderField, string orderType, int pageIndex, int pageSize, ref int count)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
string OrderBy = " order by VENDORCODE desc ";
|
|
//if (!string.IsNullOrEmpty(orderField))
|
|
// OrderBy = "Order By " + orderField + " " + orderType + "";
|
|
//else
|
|
// OrderBy = "order by (select 0)";
|
|
//strSql.Append(sql+@" Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
|
|
//strSql.Append(" As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " " + Droptable);
|
|
//count = Convert.ToInt32(ExecuteScalar(CommandType.Text, sql + " Select Count(1) From " + sqlTempName +" "+ Droptable, param));
|
|
//IDataReader dr = ExecuteReader(CommandType.Text, strSql.ToString(), param);
|
|
//return DatabaseReader.ReaderToDataTable(dr);
|
|
|
|
using (SqlConnection conn = SqlHelper.GetDataCenterConn())
|
|
{
|
|
conn.Open();
|
|
sql += " Select Count(1) From " + sqlTempName;
|
|
sql += " Select * From (Select ROW_NUMBER() Over (" + OrderBy + ") ";
|
|
sql += " As rowNum, * From " + sqlTempName + " ) As N Where rowNum > " + num + " And rowNum <= " + num1 + " ";
|
|
sql += Droptable;
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataSet ds = new DataSet();
|
|
adapter.Fill(ds);
|
|
string ss = ds.Tables[1].Rows[0][0].ToString();
|
|
count = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString());
|
|
return ds.Tables[0];
|
|
}
|
|
}
|
|
public static string GetZZNumber(string ClassCode)
|
|
{
|
|
string result = string.Empty;
|
|
switch (ClassCode)
|
|
{
|
|
case "1":
|
|
result = "YW";
|
|
break;
|
|
case "2":
|
|
result = "BW";
|
|
break;
|
|
case "3":
|
|
result = "CW";
|
|
break;
|
|
case "4":
|
|
result = "FW";
|
|
break;
|
|
case "5":
|
|
result = "PW";
|
|
break;
|
|
case "6":
|
|
result = "DW";
|
|
break;
|
|
case "7":
|
|
result = "GW";
|
|
break;
|
|
case "8":
|
|
result = "LW";
|
|
break;
|
|
}
|
|
return result;
|
|
}
|
|
public static DataTable GetDataSetBySql(string sql, SqlCommand cmd)
|
|
{
|
|
cmd.CommandText = sql;
|
|
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
|
|
DataTable ds = new DataTable();
|
|
adapter.Fill(ds);
|
|
return ds;
|
|
|
|
}
|
|
public static void UserPwdValid(string User, string Pwd)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(User))
|
|
{
|
|
Regex regex = new Regex(@"^(?=.*[A-Za-z])(?=.*\d)(?=.*[~?!@#$%^&*_-])[A-Za-z\d~?!@#$%^&*_-]{8,}$");
|
|
//string rex = @"^(?=.*[A-Za-z])(?=.*\d)(?=.*[~?!@#$%^&*_-])[A-Za-z\d~?!@#$%^&*_-]{8,}$";
|
|
if (!regex.IsMatch(Pwd))
|
|
{
|
|
throw new Exception("密码不符合规则,至少8位,至少包含一个英文字母(区分大小写),至少包含一个数字,至少包含一个特殊字符.");
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 获取配置文件信息
|
|
/// </summary>
|
|
/// <param name="F_EnCode"></param>
|
|
/// <returns></returns>
|
|
public static string GetItemsDetails(string F_EnCode, string WorkPoint)
|
|
{
|
|
string msg = string.Empty;
|
|
string sqldetail = @"SELECT a.F_ItemName,a.F_Description FROM Sys_SRM_ItemsDetail a
|
|
LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
|
|
WHERE b.F_EnCode = '{0}' and a.F_EnabledMark='1' and a.F_ItemCode='{1}'";
|
|
sqldetail = string.Format(sqldetail, F_EnCode, WorkPoint);
|
|
DataTable dts = GetDataTableBySql(sqldetail);
|
|
if (dts.Rows.Count > 0)
|
|
{
|
|
msg = dts.Rows[0]["F_Description"].ToString();
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 订单发布邮件内容
|
|
/// </summary>
|
|
/// <param name="F_EnCode"></param>
|
|
/// <returns></returns>
|
|
public static string GetItemsDetailsbySendMail(string F_EnCode)
|
|
{
|
|
string msg = string.Empty;
|
|
string sqldetail = @"SELECT a.F_ItemName,a.F_Description FROM Sys_SRM_ItemsDetail a
|
|
LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
|
|
WHERE b.F_EnCode = '{0}' and a.F_EnabledMark='1'";
|
|
sqldetail = string.Format(sqldetail, F_EnCode);
|
|
DataTable dts = GetDataTableBySql(sqldetail);
|
|
if (dts.Rows.Count > 0)
|
|
{
|
|
msg = dts.Rows[0]["F_Description"].ToString();
|
|
}
|
|
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 送货单审核配置
|
|
/// </summary>
|
|
/// <param name="F_EnCode"></param>
|
|
/// <returns></returns>
|
|
public static string GetSHDZDSHItemsDetails(string F_EnCode, string WorkPoint)
|
|
{
|
|
string msg = string.Empty;
|
|
string sqldetail = @"SELECT a.F_ItemName,a.F_Description FROM Sys_SRM_ItemsDetail a
|
|
LEFT JOIN Sys_SRM_Items b ON a.F_ItemId = b.F_Id
|
|
WHERE b.F_EnCode = '{0}' and a.F_EnabledMark='1'";
|
|
sqldetail = string.Format(sqldetail, F_EnCode, WorkPoint);
|
|
DataTable dts = GetDataTableBySql(sqldetail);
|
|
if (dts.Rows.Count > 0)
|
|
{
|
|
msg = dts.Rows[0]["F_ItemName"].ToString();
|
|
}
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 方法一：获取编号 返回Dictionary字典，调用该方法用Dictionary字典接收，只需要遍历Dictionary即可得到响应的值
|
|
/// </summary>
|
|
/// <param name="workPointCode"></param>
|
|
/// <param name="tbName"></param>
|
|
/// <param name="colName"></param>
|
|
/// <param name="Pre"></param>
|
|
/// <param name="numLen"></param>
|
|
/// <returns>返回Dictionary字典</returns>
|
|
public static Dictionary<string, int> GetAllCode(string cAcc_Id, string cVouchType, string iAmount)
|
|
{
|
|
string iFatherId = string.Empty, iChildId = string.Empty;
|
|
Dictionary<string, int> dic = new Dictionary<string, int>();
|
|
try
|
|
{
|
|
//SqlConnection conn = SqlHelper.GetDataCenterConn();
|
|
SqlConnection conn = new SqlConnection(DataCenterU8ConnString);
|
|
SqlCommand cmd = new SqlCommand("sp_GetIDWithoutRemote", conn);
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.Parameters.AddWithValue("@cAcc_Id", cAcc_Id); //给输入参数赋值
|
|
cmd.Parameters.AddWithValue("@cVouchType", cVouchType); //给输入参数赋值
|
|
cmd.Parameters.AddWithValue("@iAmount", iAmount); //给输入参数赋值
|
|
//cmd.Parameters.AddWithValue("@iFatherId", iFatherId); //给输入参数赋值
|
|
//cmd.Parameters.AddWithValue("@iChildId", iChildId); //给输入参数赋值
|
|
SqlParameter parOutput = cmd.Parameters.Add("@iFatherId", SqlDbType.NVarChar, 50); //定义输出参数
|
|
cmd.Parameters["@iFatherId"].Direction = ParameterDirection.Output;
|
|
SqlParameter parOutputs = cmd.Parameters.Add("@iChildId", SqlDbType.NVarChar, 50); //定义输出参数
|
|
cmd.Parameters["@iChildId"].Direction = ParameterDirection.Output;
|
|
SqlParameter parReturn = new SqlParameter("@return", SqlDbType.Int);
|
|
parReturn.Direction = ParameterDirection.ReturnValue; //参数类型为ReturnValue
|
|
cmd.Parameters.Add(parReturn);
|
|
conn.Open();
|
|
cmd.ExecuteNonQuery();
|
|
iFatherId = cmd.Parameters["@iFatherId"].Value.ToString();
|
|
iChildId = cmd.Parameters["@iChildId"].Value.ToString();
|
|
if (!string.IsNullOrWhiteSpace(iFatherId))//判断iFatherId是否为空，不为空的话将值放入dictionary字典中，否则int.Parse()会抛出异常
|
|
{
|
|
dic.Add("iFatherId", int.Parse(iFatherId));
|
|
dic.Add("iChildId", int.Parse(iChildId));
|
|
}
|
|
}
|
|
catch (System.Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
return dic;
|
|
}
|
|
/// <summary>
|
|
/// DataSet转Json字符串，主子结构
|
|
/// 需要建立主子表关系，第一张表为主表
|
|
/// 会排除关联列
|
|
/// </summary>
|
|
/// <param name="dataSet"></param>
|
|
/// <param name="RelationName">关系名称</param>
|
|
/// <returns></returns>
|
|
public static string DataSetToJson(DataSet dataSet, string RelationName, string column)
|
|
{
|
|
DataRelation dataRelation = new DataRelation(RelationName, dataSet.Tables[0].Columns[column], dataSet.Tables[1].Columns[column]);
|
|
dataSet.Relations.Add(dataRelation);
|
|
|
|
StringBuilder jsonString = new StringBuilder();
|
|
//foreach (DataTable table in dataSet.Tables)
|
|
//{
|
|
DataTable table = dataSet.Tables[0];
|
|
|
|
jsonString.Append("[");
|
|
DataRowCollection drc = table.Rows;
|
|
for (int i = 0; i < drc.Count; i++)
|
|
{
|
|
DataRow dataRow = drc[i];
|
|
jsonString.Append("{");
|
|
for (int j = 0; j < table.Columns.Count; j++)
|
|
{
|
|
string strKey = table.Columns[j].ColumnName;
|
|
if (dataSet.Relations[RelationName].ParentColumns.Select(a => a.Caption).Contains(strKey))
|
|
continue;
|
|
string strValue = dataRow[j].ToString();
|
|
Type type = table.Columns[j].DataType;
|
|
jsonString.Append("\"" + strKey + "\":");
|
|
strValue = StringFormat(strValue, type);
|
|
if (j < table.Columns.Count - 1)
|
|
{
|
|
jsonString.Append(strValue + ",");
|
|
}
|
|
else
|
|
{
|
|
jsonString.Append(strValue);
|
|
}
|
|
}
|
|
jsonString.Append(",\"" + RelationName + "\":");
|
|
DataRow[] drs = dataRow.GetChildRows(RelationName);
|
|
jsonString.Append("[");
|
|
foreach (DataRow dr in drs)
|
|
{
|
|
DataTable dt = dr.Table;
|
|
jsonString.Append("{");
|
|
for (int j = 0; j < dt.Columns.Count; j++)
|
|
{
|
|
string strKey = dt.Columns[j].ColumnName;
|
|
if (dataSet.Relations[RelationName].ChildColumns.Select(a => a.Caption).Contains(strKey))
|
|
continue;
|
|
string strValue = dr[j].ToString();
|
|
Type type = dt.Columns[j].DataType;
|
|
jsonString.Append("\"" + strKey + "\":");
|
|
strValue = StringFormat(strValue, type);
|
|
if (j < dt.Columns.Count - 1)
|
|
{
|
|
jsonString.Append(strValue + ",");
|
|
}
|
|
else
|
|
{
|
|
jsonString.Append(strValue);
|
|
}
|
|
}
|
|
jsonString.Append("},");
|
|
}
|
|
if (drs.Length > 0)
|
|
jsonString.Remove(jsonString.Length - 1, 1);
|
|
jsonString.Append("]");
|
|
|
|
jsonString.Append("},");
|
|
}
|
|
jsonString.Remove(jsonString.Length - 1, 1);
|
|
jsonString.Append("]");
|
|
//}
|
|
string res = jsonString.ToString();
|
|
return res;
|
|
}
|
|
/// <summary>
|
|
/// 格式化字符型、日期型、布尔型
|
|
/// </summary>
|
|
/// <param name="str"></param>
|
|
/// <param name="type"></param>
|
|
/// <returns></returns>
|
|
private static string StringFormat(string str, Type type)
|
|
{
|
|
if (type == typeof(string))
|
|
{
|
|
str = String2Json(str);
|
|
str = "\"" + str + "\"";
|
|
}
|
|
else if (type == typeof(DateTime))
|
|
{
|
|
str = "\"" + str + "\"";
|
|
}
|
|
else if (type == typeof(bool))
|
|
{
|
|
str = str.ToLower();
|
|
}
|
|
else if (type != typeof(string) && string.IsNullOrEmpty(str))
|
|
{
|
|
str = "\"" + str + "\"";
|
|
}
|
|
return str;
|
|
}
|
|
/// <summary>
|
|
/// 过滤特殊字符
|
|
/// </summary>
|
|
/// <param name="s">字符串</param>
|
|
/// <returns>json字符串</returns>
|
|
private static string String2Json(String s)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
for (int i = 0; i < s.Length; i++)
|
|
{
|
|
char c = s.ToCharArray()[i];
|
|
switch (c)
|
|
{
|
|
case '\"':
|
|
sb.Append("\\\""); break;
|
|
case '\\':
|
|
sb.Append("\\\\"); break;
|
|
case '/':
|
|
sb.Append("\\/"); break;
|
|
case '\b':
|
|
sb.Append("\\b"); break;
|
|
case '\f':
|
|
sb.Append("\\f"); break;
|
|
case '\n':
|
|
sb.Append("\\n"); break;
|
|
case '\r':
|
|
sb.Append("\\r"); break;
|
|
case '\t':
|
|
sb.Append("\\t"); break;
|
|
default:
|
|
sb.Append(c); break;
|
|
}
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
public static string GetEATTRIBUTE(string Tabname, string TablenameJC)
|
|
{
|
|
string SqlKey = "";
|
|
string sql = @"select TableCode, ColumnCode AS Code,Name from ICSSRMColumnEnable
|
|
where Enable=1 and TableCode='" + Tabname + "' order by MTIME";
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
SqlKey += "" + TablenameJC + "." + dt.Rows[i]["Code"].ToString() + ",";
|
|
}
|
|
return SqlKey;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataRow
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="ConnectStr">数据库链接名称</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataRow</returns>
|
|
public static DataRow GetDataRowBySql_OtherConn(string sql, string ConnectStr, params SqlParameter[] spArr)
|
|
{
|
|
DataTable dt = GetDataTableBySql_OtherConn(sql, ConnectStr, spArr);
|
|
if (dt == null || dt.Rows.Count == 0)
|
|
return null;
|
|
|
|
return dt.Rows[0];
|
|
}
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataTable 分页显示
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="ConnectStr">数据库链接名称</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableBySql_OtherConn(string sql, string ConnectStr, ref Pagination jqgridparam, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetConnByStr(ConnectStr))
|
|
{
|
|
|
|
string orderField = jqgridparam.sidx;
|
|
string orderType = jqgridparam.sord;
|
|
int pageIndex = jqgridparam.page;
|
|
int pageSize = jqgridparam.rows;
|
|
int totalRow = 0;
|
|
string OrderBy = "";
|
|
if (pageIndex == 0)
|
|
{
|
|
pageIndex = 1;
|
|
}
|
|
int num = (pageIndex - 1) * pageSize;
|
|
int num1 = (pageIndex) * pageSize;
|
|
|
|
if (!string.IsNullOrEmpty(orderField))
|
|
OrderBy = orderField + " " + orderType + "";
|
|
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr != null && spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
DataTable dt = cmd.ExecuteDataTable();
|
|
DataTable dtNew = dt.Clone();
|
|
if (dt != null && dt.Rows.Count > 0)
|
|
{
|
|
jqgridparam.records = dt.Rows.Count;
|
|
DataView dv = dt.DefaultView;
|
|
dv.Sort = OrderBy;
|
|
dt = dv.ToTable();
|
|
|
|
if (dt.Rows.Count >= num)
|
|
{
|
|
for (int i = num; i < num1 && i < dt.Rows.Count; i++)
|
|
{
|
|
dtNew.ImportRow(dt.Rows[i]);
|
|
}
|
|
}
|
|
}
|
|
|
|
return dtNew;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 根据sql语句和参数，返回DataTable
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <param name="ConnectStr">数据库链接名称</param>
|
|
/// <param name="spArr">可变参数</param>
|
|
/// <returns>DataTable</returns>
|
|
public static DataTable GetDataTableBySql_OtherConn(string sql, string ConnectStr, params SqlParameter[] spArr)
|
|
{
|
|
using (SqlConnection conn = SqlHelper.GetConnByStr(ConnectStr))
|
|
{
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand(sql, conn).AddTimeout();
|
|
if (spArr != null && spArr.Length > 0)
|
|
cmd.Parameters.AddRange(spArr.SetDBNull());
|
|
DataTable dt = cmd.ExecuteDataTable();
|
|
return dt;
|
|
}
|
|
}
|
|
}//end of class
|
|
|
|
#region [ SqlHelper 的扩展方法类 ]
|
|
/// <summary>
|
|
/// 扩展方法类
|
|
/// </summary>
|
|
public static class SqlHelperExtensionMethods
|
|
{
|
|
/// <summary>
|
|
/// 新建SqlCommand对象时， 自动加上指定的 CommandTimeout. by ngye, on 2013-07-11.
|
|
/// </summary>
|
|
/// <param name="cmd">SqlCommand对象</param>
|
|
/// <returns>SqlCommand对象</returns>
|
|
public static SqlCommand AddTimeout(this SqlCommand cmd)
|
|
{
|
|
cmd.CommandTimeout = SqlHelper.CommandTimeout;
|
|
return cmd;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 新建SqlBulkCopy对象时， 自动加上指定的 BulkCopyTimeout. by ngye, on 2013-08-30.
|
|
/// </summary>
|
|
/// <param name="cmd">SqlBulkCopy对象</param>
|
|
/// <returns>SqlBulkCopy对象</returns>
|
|
public static SqlBulkCopy AddTimeout(this SqlBulkCopy bulkCopy)
|
|
{
|
|
bulkCopy.BulkCopyTimeout = SqlHelper.CommandTimeout;
|
|
return bulkCopy;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行cmd得到 DataTable. by ngye, on 2013-08-01
|
|
/// </summary>
|
|
/// <param name="cmd"></param>
|
|
/// <returns></returns>
|
|
public static DataTable ExecuteDataTable(this SqlCommand cmd)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
|
|
{
|
|
adapter.Fill(dt);
|
|
}
|
|
return dt;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 为SqlParameter设置参数. by ngye, on 2013-08-15.
|
|
/// </summary>
|
|
/// <param name="sp"></param>
|
|
/// <returns></returns>
|
|
public static SqlParameter SetValue(this SqlParameter sp, object value)
|
|
{
|
|
sp.Value = value;
|
|
return sp;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 为SqlParameter设置SqlDbType. by ngye, on 2013-09-03.
|
|
/// </summary>
|
|
/// <param name="sp"></param>
|
|
/// <returns></returns>
|
|
public static SqlParameter SetSqlDbType(this SqlParameter sp, SqlDbType dbType)
|
|
{
|
|
sp.SqlDbType = dbType;
|
|
return sp;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 对可以为空的值作这样的处理，一旦其为空，就设置为DBNull.value.
|
|
/// </summary>
|
|
/// <param name="sp"></param>
|
|
/// <returns></returns>
|
|
public static SqlParameter[] SetDBNull(this SqlParameter[] spArr)
|
|
{
|
|
if (spArr == null || spArr.Length == 0)
|
|
return spArr;
|
|
for (int i = 0; i < spArr.Length; i++)
|
|
{
|
|
SqlParameter sp = spArr[i];
|
|
if (sp.Value == null)
|
|
sp.Value = DBNull.Value;
|
|
}
|
|
return spArr;
|
|
}
|
|
public static string HttpPost(string url, string body)
|
|
{
|
|
try
|
|
{
|
|
Encoding encoding = Encoding.UTF8;
|
|
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
|
|
request.Method = "POST";
|
|
request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
|
|
request.ContentType = "application/json; charset=utf-8";
|
|
|
|
byte[] buffer = encoding.GetBytes(body);
|
|
request.ContentLength = buffer.Length;
|
|
request.GetRequestStream().Write(buffer, 0, buffer.Length);
|
|
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
|
|
using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
|
|
{
|
|
return reader.ReadToEnd();
|
|
}
|
|
}
|
|
catch (WebException ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
#endregion
|
|
}
|