745 lines
30 KiB
C#
745 lines
30 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
|
||
namespace DataLinkMesWork
|
||
{
|
||
public partial class DataLink
|
||
{
|
||
/// <summary>
|
||
/// 通过执行SQL语句，查询数据库
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="ds"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteDataset(string sql, out DataSet ds)
|
||
{
|
||
string errorMessage;
|
||
return SQLCommon.ExecuteDataset(sql, connectionString, out ds, out errorMessage);
|
||
}
|
||
/// <summary>
|
||
/// 通过执行SQL语句，实现对表的增、删、改
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteNonQuery(string sql)
|
||
{
|
||
string errorMessage;
|
||
return SQLCommon.ExecuteNonQuery( sql, connectionString, out errorMessage);
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 通过执行SQL语句，实现对表的增、删、改。返回后面Select 参数 行数或自动编号
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="count"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteOutNum(string sql, out int count)
|
||
{
|
||
string errorMessage;
|
||
return SQLCommon.ExecuteOutNum(sql, connectionString, out count, out errorMessage);
|
||
|
||
}
|
||
/// <summary>
|
||
/// 执行带参数的SQL语句，完成增、删、改
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteSql(string sql, out string errorMessage)
|
||
{
|
||
return SQLCommon.ExecuteSql(sql, connectionString, out errorMessage);
|
||
}
|
||
/// <summary>
|
||
/// 执行带参数的SQL语句，完成增、删、改
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteSql(string sql, ref ParamData[] paramData, out string errorMessage)
|
||
{
|
||
SqlParameter[] thisParms;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
return SQLCommon.ExecuteSql(sql, connectionString, ref thisParms, out errorMessage);
|
||
}
|
||
/// <summary>
|
||
/// 执行带参数的SQL语句，完成增、删、改
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteSql(string sql, out DataTable dt, out string errorMessage)
|
||
{
|
||
return SQLCommon.ExecuteSql(sql, connectionString, out dt, out errorMessage);
|
||
}
|
||
/// <summary>
|
||
/// 执行带参数的SQL语句，完成增、删、改
|
||
/// </summary>
|
||
/// <param name="sql"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteSql(string sql, ref ParamData[] paramData,out DataTable dt, out string errorMessage)
|
||
{
|
||
SqlParameter[] thisParms;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
return SQLCommon.ExecuteSql(sql, ref thisParms, connectionString, out dt,out errorMessage);
|
||
}
|
||
/// <summary>
|
||
/// 分页查询
|
||
/// </summary>
|
||
/// <param name="tbname"></param>
|
||
/// <param name="fieldkey"></param>
|
||
/// <param name="where"></param>
|
||
/// <param name="fieldshow"></param>
|
||
/// <param name="fieldorder"></param>
|
||
/// <param name="fieldgroup"></param>
|
||
/// <param name="pagecurrent"></param>
|
||
/// <param name="pagesize"></param>
|
||
/// <param name="pagecount"></param>
|
||
/// <param name="itemcount"></param>
|
||
/// <param name="dt"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecPageQueryMesWork(string tbname, string fieldkey, string where, string fieldshow, string fieldorder, string fieldgroup, int pagecurrent, int pagesize, out int pagecount, out int itemcount, out DataTable dt)
|
||
{
|
||
string ErrorMessage;
|
||
return DataAccess.ExecPageQueryMesWork(tbname, connectionString, fieldkey, where, fieldshow, fieldorder, fieldgroup, pagecurrent, pagesize, out pagecount, out itemcount, out dt, out ErrorMessage);
|
||
}
|
||
/// <summary>
|
||
/// INSERT INTO [Test]([f1],f2) VALUES(@f1,@f2)
|
||
/// </summary>
|
||
/// <param name="tableName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <returns></returns>
|
||
public static bool Insert(string tableName, ParamData[] paramData,out string err)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms;
|
||
string sqlCmd;
|
||
sqlCmd = GetSqlCmd(tableName, paramData, out thisParms);
|
||
|
||
return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionString,out err);
|
||
}
|
||
/// <summary>
|
||
/// 执行增、删、改存储过程
|
||
/// </summary>
|
||
/// <param name="connString"></param>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string connString,string procedureName, ref ParamData[] paramData, out string errorMessage)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms = null;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
if (SQLCommon.ExecuteStoredProcedure(procedureName, connString, ref thisParms, out errorMessage))
|
||
{
|
||
for (int i = 0; i < thisParms.Length; i++)
|
||
{
|
||
if (paramData[i].isOutPut)
|
||
{
|
||
paramData[i].value = thisParms[i].Value.ToString();
|
||
}
|
||
}
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
/// <summary>
|
||
/// 执行增、删、改存储过程
|
||
/// </summary>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref ParamData[] paramData, out string errorMessage)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms=null;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
if(SQLCommon.ExecuteStoredProcedure(procedureName, connectionString, ref thisParms, out errorMessage))
|
||
{
|
||
for(int i=0;i< thisParms.Length;i++)
|
||
{
|
||
if(paramData[i].isOutPut)
|
||
{
|
||
paramData[i].value = thisParms[i].Value.ToString();
|
||
}
|
||
}
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 执行增、删、改存储过程
|
||
/// </summary>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] thisParms, out string errorMessage)
|
||
{
|
||
return SQLCommon.ExecuteStoredProcedure(procedureName, connectionString, ref thisParms, out errorMessage);
|
||
}
|
||
|
||
public static bool ExecuteStoredProcedureWithConnectionString(string procedureName, ref ParamData[] paramData, out string errorMessage,string conString)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms = null;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
if (SQLCommon.ExecuteStoredProcedure(procedureName, conString, ref thisParms, out errorMessage))
|
||
{
|
||
for (int i = 0; i < thisParms.Length; i++)
|
||
{
|
||
if (paramData[i].isOutPut)
|
||
{
|
||
paramData[i].value = thisParms[i].Value.ToString();
|
||
}
|
||
}
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
/// <summary>
|
||
/// 插入数据，可以返回自动编号。存储过程增加 select @@identity
|
||
/// </summary>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <param name="index"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref ParamData[] paramData, out long index,out string errorMessage)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms = null;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
if (SQLCommon.ExecuteStoredProcedure(procedureName, connectionString, ref thisParms, out index,out errorMessage))
|
||
{
|
||
for (int i = 0; i < thisParms.Length; i++)
|
||
{
|
||
if (paramData[i].isOutPut)
|
||
{
|
||
paramData[i].value = thisParms[i].Value.ToString();
|
||
}
|
||
}
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
/// <summary>
|
||
/// 用存储过程查询数据
|
||
/// </summary>
|
||
/// <param name="procedureName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <param name="dt"></param>
|
||
/// <param name="errorMessage"></param>
|
||
/// <returns></returns>
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref ParamData[] paramData, out DataTable dt, out string errorMessage)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms = null;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
return SQLCommon.ExecuteStoredProcedure(procedureName, connectionString, ref thisParms, out dt, out errorMessage);
|
||
|
||
}
|
||
public static bool ExecuteStoredProcedure(string procedureName, ref ParamData[] paramData, out DataSet ds, out string errorMessage)
|
||
{
|
||
//INSERT INTO [Test](f1,f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms = null;
|
||
GetSqlCmd(paramData, out thisParms);
|
||
return SQLCommon.ExecuteStoredProcedure(procedureName, connectionString, ref thisParms, out ds, out errorMessage);
|
||
|
||
}
|
||
// ExecuteStoredProcedure(string procedureName, string connectionString, ref SqlParameter[] sqlParameters, out DataSet ds, out string errorMessage)
|
||
/// <summary>
|
||
/// 将数据插入数据库
|
||
/// 输入表名
|
||
/// 输入字段对应的值
|
||
/// 返回自动编号
|
||
/// </summary>
|
||
/// <param name="tableName">输入表名</param>
|
||
/// <param name="paramData">输入字段对应的值</param>
|
||
/// <param name="id">返回自动编号</param>
|
||
/// <param name="err">返回插入失败的原因</param>
|
||
/// <returns></returns>
|
||
public static bool Insert(string tableName, ParamData[] paramData, out long id,out string err)
|
||
{
|
||
//INSERT INTO [Test]([f1],f2) VALUES(@f1,@f2)
|
||
SqlParameter[] thisParms;
|
||
string sqlCmd;
|
||
sqlCmd = GetSqlCmd(tableName, paramData, out thisParms);
|
||
return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionString, out id,out err);
|
||
|
||
}
|
||
public static bool Insert(System.Data.DataTable dt, out string err)
|
||
{
|
||
SqlParameter[] thisParms;
|
||
string sqlCmd;
|
||
sqlCmd = GetSqlCmd(dt, out thisParms);
|
||
return SQLCommon.ExecuteSqlCmd(sqlCmd, connectionString, ref thisParms, dt, out err);
|
||
}
|
||
/// <summary>
|
||
/// 根据条件，更新数据库
|
||
/// </summary>
|
||
/// <param name="tableName">被更新的表</param>
|
||
/// <param name="paramSet">更改的字段与值对应表</param>
|
||
/// <param name="paramWhere">更改的字段与值对应表的条件</param>
|
||
/// <returns></returns>
|
||
public static bool UpData(string tableName, ParamData[] paramSet, ParamData[] paramWhere, out string err)
|
||
{
|
||
//UPDATE Test SET [f1] = @f1,[f2] = @f2,[f3] = @f3 WHERE f1=@ff1 and f2=@ff2
|
||
SqlParameter[] thisParms;
|
||
string sqlCmd;
|
||
sqlCmd = GetSqlCmdUpdate( tableName, paramSet, paramWhere, out thisParms);
|
||
return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionString, out err);
|
||
}
|
||
/// <summary>
|
||
/// DELETE FROM TEST WHERE f1=@f1
|
||
/// </summary>
|
||
/// <param name="tableName"></param>
|
||
/// <param name="paramWhere">WHERE f1=@f1 @f1,value</param>
|
||
/// <returns></returns>
|
||
public static bool Delete(string tableName, ParamData[] paramWhere, out string err)
|
||
{
|
||
SqlParameter[] thisParms;
|
||
string sqlCmd;
|
||
sqlCmd = GetSqlCmdDelete(tableName, paramWhere, out thisParms);
|
||
return SQLCommon.ExecuteInsertMesWork(sqlCmd, ref thisParms, connectionString, out err);
|
||
}
|
||
/// <summary>
|
||
/// SELECT f1,f2 from test where f1=@f1 and f1=@f2
|
||
/// </summary>
|
||
/// <param name="selectCmd"></param>
|
||
/// <param name="paramData">ParamData数组赋值(f1,value1),(f2,value2)</param>
|
||
/// <param name="dt"></param>
|
||
/// <param name="err"></param>
|
||
/// <returns></returns>
|
||
public static bool Select(string selectCmd, ParamData[] paramData, out DataTable dt, out string err)
|
||
{
|
||
//SELECT f1,f2 from test where f1=@f1 and f1=@f2
|
||
SqlParameter[] thisParms;
|
||
GetSqlCmdSelect(paramData, out thisParms);
|
||
return SQLCommon.ExecuteSelectMesWork(selectCmd, ref thisParms, connectionString, out dt,out err);
|
||
}
|
||
|
||
/// <summary>
|
||
///paramData: @tbname sysname, --要分页显示的表名
|
||
/// @fieldkey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
|
||
/// @fieldshow nvarchar(1000), --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
|
||
/// @fieldOrder nvarchar(1000), ----以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC --用于指定排序顺序
|
||
/// @PageCurrent int=1, --要显示的页码
|
||
/// @PageSize int=30, --每页的大小(记录数)
|
||
///paramDataWhere: @where nvarchar(1000), --查询条件
|
||
///@PageCount int OUTPUT, --总页数
|
||
///@ItemCount int output --数据总数
|
||
/// </summary>
|
||
/// <param name="paramData[6]">tbname,fieldkey,fieldshow,fieldorder,pagecurrent,pagesize</param>
|
||
/// <param name="paramDataWhere"></param>
|
||
/// <param name="dt"></param>
|
||
/// <param name="pagecount"></param>
|
||
/// <param name="itemcount"></param>
|
||
/// <param name="err"></param>
|
||
public static bool SelectPage(string tbname, string fieldkey, string where, string fieldshow, string fieldorder, string fieldgroup, int pagecurrent, int pagesize, out int pagecount, out int itemcount, out DataTable dt)
|
||
{
|
||
string err = "";
|
||
dt = null;
|
||
return DataAccess.ExecPageQueryMesWork(tbname, connectionString, fieldkey, where, fieldshow, fieldorder, fieldgroup,pagecurrent, pagesize, out pagecount, out itemcount, out dt, out err);
|
||
}
|
||
|
||
static private string SelectPageGetLogicalOperator(string logicaloperator)
|
||
{
|
||
string logicalOperator = "=";
|
||
if (logicaloperator != null)
|
||
{
|
||
if (logicaloperator.Length > 0)
|
||
{
|
||
logicalOperator = logicaloperator;
|
||
}
|
||
}
|
||
return logicalOperator;
|
||
}
|
||
|
||
static private string SelectPageGetValue(string type,string value)
|
||
{
|
||
string itemValue="";
|
||
switch (type)
|
||
{
|
||
case "string":
|
||
itemValue = "'"+value+"'";
|
||
break;
|
||
case "int":
|
||
case "float":
|
||
itemValue = value;
|
||
break;
|
||
case "bool":
|
||
if(value=="true")
|
||
{
|
||
value = "1";
|
||
}
|
||
if (value == "false")
|
||
{
|
||
value = "0";
|
||
}
|
||
itemValue = value;
|
||
break;
|
||
case "datetime":
|
||
itemValue = "'"+value+"'";
|
||
break;
|
||
default:
|
||
itemValue = "'" + value + "'";
|
||
break;
|
||
}
|
||
return itemValue;
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// UPDATE Test SET [f1] = @f1,[f2] = @f2,[f3] = @f3 WHERE f1=@ff1 and f2=@ff2
|
||
/// </summary>
|
||
/// <param name="tableName"></param>
|
||
/// <param name="paramSet"></param>
|
||
/// <param name="paramWhere"></param>
|
||
/// <param name="thisParms"></param>
|
||
/// <returns></returns>
|
||
private static string GetSqlCmdUpdate(string tableName, ParamData[] paramSet, ParamData[] paramWhere, out SqlParameter[] thisParms)
|
||
{
|
||
string sqlStr = "";
|
||
string sqlStrSet = "";
|
||
string sqlStrWhere = "";
|
||
//string sqlStrParam = "";
|
||
string sqlCmd;
|
||
thisParms = null;
|
||
object itemValue;
|
||
sqlStr = "UPDATE " + tableName+" SET ";
|
||
if (paramWhere != null)
|
||
{
|
||
if (paramWhere.Length > 0)
|
||
{
|
||
thisParms = new SqlParameter[paramWhere.Length + paramSet.Length];
|
||
for (int i = 0; i < paramWhere.Length; i++)
|
||
{
|
||
sqlStrWhere = sqlStrWhere + " (" + paramWhere[i].name + "=" + "@" + paramWhere[i].name + ") and";
|
||
//itemValue = GetInputStringValue(paramWhere[i].datatype, paramWhere[i].value);
|
||
thisParms[i] = new SqlParameter("@" + paramWhere[i].name, paramWhere[i].value);
|
||
}
|
||
//去掉 "and"
|
||
char[] TrimChar = { 'a', 'n', 'd' }; //此处使用了转义字符如：\',\",\\，分别表示单引号，双引号，反斜杠
|
||
sqlStrWhere = " WHERE " + sqlStrWhere.Trim(TrimChar);
|
||
}
|
||
else
|
||
{
|
||
thisParms = new SqlParameter[paramSet.Length];
|
||
}
|
||
}
|
||
else
|
||
{
|
||
thisParms = new SqlParameter[paramSet.Length];
|
||
}
|
||
//UPDATE Test SET [f1] = @f1,[f2] = @f2,[f3] = @f3 WHERE f1=@ff1 and f2=@ff2
|
||
if (paramSet!=null)
|
||
{
|
||
if(paramSet.Length>0)
|
||
{
|
||
for(int i =0; i< paramSet.Length; i++)
|
||
{
|
||
sqlStrSet = sqlStrSet + paramSet[i].name + "=" + "@" + paramSet[i].name + ",";
|
||
//itemValue = GetInputStringValue(paramSet[i].datatype, paramSet[i].value);
|
||
thisParms[i+ paramWhere.Length] = new SqlParameter("@" + paramSet[i].name, paramSet[i].value);
|
||
}
|
||
sqlStrSet = sqlStrSet.Trim(',');
|
||
}
|
||
|
||
}
|
||
sqlCmd = sqlStr + sqlStrSet + sqlStrWhere;// + sqlStrParam;
|
||
return sqlCmd;
|
||
}
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="tableName"></param>
|
||
/// <param name="paramData"></param>
|
||
/// <param name="thisParms"></param>
|
||
/// <returns></returns>
|
||
private static string GetSqlCmdDelete(string tableName, ParamData[] paramData, out SqlParameter[] thisParms)
|
||
{
|
||
string sqlStr = "";
|
||
string sqlStrWhere = "";
|
||
string sqlCmd;
|
||
thisParms = null;
|
||
object itemValue;
|
||
sqlStr = "DELETE FROM " + tableName ;
|
||
if(paramData!=null)
|
||
{
|
||
if(paramData.Length>0)
|
||
{
|
||
thisParms = new SqlParameter[paramData.Length];
|
||
for (int i = 0; i < paramData.Length; i++)
|
||
{
|
||
sqlStrWhere = sqlStrWhere + "(" + paramData[i].name + "=" + "@" + paramData[i].name + ") and";
|
||
//itemValue = GetInputStringValue(paramData[i].datatype, paramData[i].value);
|
||
thisParms[i] = new SqlParameter("@" + paramData[i].name, paramData[i].value);
|
||
}
|
||
//去掉 "and"
|
||
char[] TrimChar = { 'a', 'n', 'd' }; //此处使用了转义字符如：\',\",\\，分别表示单引号，双引号，反斜杠
|
||
sqlStrWhere = " WHERE "+sqlStrWhere.Trim(TrimChar);
|
||
}
|
||
}
|
||
sqlCmd = sqlStr + sqlStrWhere;
|
||
return sqlCmd;
|
||
}
|
||
private static string GetSqlCmd(string tableName, ParamData[] paramData, out SqlParameter[] thisParms)
|
||
{
|
||
string sqlStr = "";
|
||
string sqlStrField = "";
|
||
string sqlStrParam = "";
|
||
string sqlCmd;
|
||
thisParms = new SqlParameter[paramData.Length];
|
||
|
||
object itemValue;
|
||
sqlStr = sqlStr + "INSERT INTO " + tableName + "(";
|
||
for (int i = 0; i < paramData.Length; i++)
|
||
{
|
||
sqlStrField = sqlStrField + paramData[i].name + ",";
|
||
sqlStrParam = sqlStrParam + "@" + paramData[i].name + ",";
|
||
|
||
//itemValue = GetInputStringValue(paramData[i].datatype, paramData[i].value);
|
||
thisParms[i] = new SqlParameter("@" + paramData[i].name, paramData[i].value);
|
||
}
|
||
sqlStrField = sqlStrField.Trim(',') + ")";
|
||
sqlStrParam = " VALUES(" + sqlStrParam.Trim(',') + ")";
|
||
|
||
sqlCmd = sqlStr + sqlStrField + sqlStrParam;
|
||
return sqlCmd;
|
||
}
|
||
|
||
private static void GetSqlCmd( ParamData[] paramData, out SqlParameter[] thisParms)
|
||
{
|
||
if(paramData == null)
|
||
{
|
||
thisParms = null;
|
||
return;
|
||
}
|
||
thisParms = new SqlParameter[paramData.Length];
|
||
|
||
//object itemValue;
|
||
for (int i = 0; i < paramData.Length; i++)
|
||
{
|
||
//itemValue = GetInputStringValue(paramData[i].datatype, paramData[i].value);
|
||
thisParms[i] = new SqlParameter("@" + paramData[i].name, paramData[i].value);
|
||
if(paramData[i].isOutPut)
|
||
{
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
}
|
||
}
|
||
}
|
||
|
||
private static string GetSqlCmd(DataTable dt, out SqlParameter[] thisParms)
|
||
{
|
||
string sqlStr = "";
|
||
string sqlStrField = "";
|
||
string sqlStrParam = "";
|
||
string sqlCmd;
|
||
string tableName = dt.TableName;
|
||
|
||
thisParms = new SqlParameter[dt.Columns.Count];
|
||
|
||
sqlStr = sqlStr + "INSERT INTO " + tableName + "(";
|
||
for (int i = 0; i < dt.Columns.Count; i++)
|
||
{
|
||
sqlStrField = sqlStrField + dt.Columns[i].ColumnName + ",";
|
||
sqlStrParam = sqlStrParam + "@" + dt.Columns[i].ColumnName + ",";
|
||
SqlDbType sqlType;
|
||
sqlType = SqlTypeString2SqlType(dt.Columns[i].DataType.Name);
|
||
//itemValue = GetInputStringValue(paramData[i].datatype, paramData[i].value);
|
||
thisParms[i] = new SqlParameter("@" + dt.Columns[i].ColumnName, sqlType);
|
||
thisParms[i].SourceColumn = dt.Columns[i].ColumnName;
|
||
thisParms[i].IsNullable = true;
|
||
}
|
||
sqlStrField = sqlStrField.Trim(',') + ")";
|
||
sqlStrParam = " VALUES(" + sqlStrParam.Trim(',') + ")";
|
||
|
||
sqlCmd = sqlStr + sqlStrField + sqlStrParam;
|
||
return sqlCmd;
|
||
}
|
||
// sql server数据类型（如：varchar）
|
||
// 转换为SqlDbType类型
|
||
public static SqlDbType SqlTypeString2SqlType(string sqlTypeString)
|
||
{
|
||
SqlDbType dbType = SqlDbType.Variant;//默认为Object
|
||
|
||
switch (sqlTypeString)
|
||
{
|
||
case "Int32":
|
||
dbType = SqlDbType.Int;
|
||
break;
|
||
case "varchar":
|
||
dbType = SqlDbType.VarChar;
|
||
break;
|
||
case "bit":
|
||
dbType = SqlDbType.Bit;
|
||
break;
|
||
case "DateTime":
|
||
dbType = SqlDbType.DateTime;
|
||
break;
|
||
case "decimal":
|
||
dbType = SqlDbType.Decimal;
|
||
break;
|
||
case "float":
|
||
dbType = SqlDbType.Float;
|
||
break;
|
||
case "image":
|
||
dbType = SqlDbType.Image;
|
||
break;
|
||
case "money":
|
||
dbType = SqlDbType.Money;
|
||
break;
|
||
case "ntext":
|
||
dbType = SqlDbType.NText;
|
||
break;
|
||
case "String":
|
||
dbType = SqlDbType.NVarChar;
|
||
break;
|
||
case "smalldatetime":
|
||
dbType = SqlDbType.SmallDateTime;
|
||
break;
|
||
case "smallint":
|
||
dbType = SqlDbType.SmallInt;
|
||
break;
|
||
case "text":
|
||
dbType = SqlDbType.Text;
|
||
break;
|
||
case "bigint":
|
||
dbType = SqlDbType.BigInt;
|
||
break;
|
||
case "binary":
|
||
dbType = SqlDbType.Binary;
|
||
break;
|
||
case "char":
|
||
dbType = SqlDbType.Char;
|
||
break;
|
||
case "nchar":
|
||
dbType = SqlDbType.NChar;
|
||
break;
|
||
case "numeric":
|
||
dbType = SqlDbType.Decimal;
|
||
break;
|
||
case "real":
|
||
dbType = SqlDbType.Real;
|
||
break;
|
||
case "smallmoney":
|
||
dbType = SqlDbType.SmallMoney;
|
||
break;
|
||
case "sql_variant":
|
||
dbType = SqlDbType.Variant;
|
||
break;
|
||
case "timestamp":
|
||
dbType = SqlDbType.Timestamp;
|
||
break;
|
||
case "tinyint":
|
||
dbType = SqlDbType.TinyInt;
|
||
break;
|
||
case "uniqueidentifier":
|
||
dbType = SqlDbType.UniqueIdentifier;
|
||
break;
|
||
case "varbinary":
|
||
dbType = SqlDbType.VarBinary;
|
||
break;
|
||
case "xml":
|
||
dbType = SqlDbType.Xml;
|
||
break;
|
||
}
|
||
return dbType;
|
||
}
|
||
// SqlDbType转换为C#数据类型
|
||
public static Type SqlType2CsharpType(SqlDbType sqlType)
|
||
{
|
||
switch (sqlType)
|
||
{
|
||
case SqlDbType.BigInt:
|
||
return typeof(Int64);
|
||
case SqlDbType.Binary:
|
||
return typeof(Object);
|
||
case SqlDbType.Bit:
|
||
return typeof(Boolean);
|
||
case SqlDbType.Char:
|
||
return typeof(String);
|
||
case SqlDbType.DateTime:
|
||
return typeof(DateTime);
|
||
case SqlDbType.Decimal:
|
||
return typeof(Decimal);
|
||
case SqlDbType.Float:
|
||
return typeof(Double);
|
||
case SqlDbType.Image:
|
||
return typeof(Object);
|
||
case SqlDbType.Int:
|
||
return typeof(Int32);
|
||
case SqlDbType.Money:
|
||
return typeof(Decimal);
|
||
case SqlDbType.NChar:
|
||
return typeof(String);
|
||
case SqlDbType.NText:
|
||
return typeof(String);
|
||
case SqlDbType.NVarChar:
|
||
return typeof(String);
|
||
case SqlDbType.Real:
|
||
return typeof(Single);
|
||
case SqlDbType.SmallDateTime:
|
||
return typeof(DateTime);
|
||
case SqlDbType.SmallInt:
|
||
return typeof(Int16);
|
||
case SqlDbType.SmallMoney:
|
||
return typeof(Decimal);
|
||
case SqlDbType.Text:
|
||
return typeof(String);
|
||
case SqlDbType.Timestamp:
|
||
return typeof(Object);
|
||
case SqlDbType.TinyInt:
|
||
return typeof(Byte);
|
||
case SqlDbType.Udt://自定义的数据类型
|
||
return typeof(Object);
|
||
case SqlDbType.UniqueIdentifier:
|
||
return typeof(Object);
|
||
case SqlDbType.VarBinary:
|
||
return typeof(Object);
|
||
case SqlDbType.VarChar:
|
||
return typeof(String);
|
||
case SqlDbType.Variant:
|
||
return typeof(Object);
|
||
case SqlDbType.Xml:
|
||
return typeof(Object);
|
||
default:
|
||
return null;
|
||
}
|
||
}
|
||
|
||
//Dictionary<Type, SqlDbType> GetSQLTypeConversionMap()
|
||
//{
|
||
// var result = new Dictionary<Type, SqlDbType>();
|
||
// result.Add(typeof(string), SqlDbType.VarChar);
|
||
// result.Add(typeof(Int16), SqlDbType.Int);
|
||
// result.Add(typeof(Int32), SqlDbType.Int);
|
||
// result.Add(typeof(DateTime), SqlDbType.DateTime2);
|
||
// return result;
|
||
//}
|
||
private static void GetSqlCmdSelect(ParamData[] paramData, out SqlParameter[] thisParms)
|
||
{
|
||
thisParms = null;
|
||
if (paramData == null) return;
|
||
thisParms = new SqlParameter[paramData.Length];
|
||
|
||
object itemValue;
|
||
for (int i = 0; i < paramData.Length; i++)
|
||
{
|
||
//itemValue = GetInputStringValue(paramData[i].datatype, paramData[i].value);
|
||
thisParms[i] = new SqlParameter("@" + paramData[i].name, paramData[i].value);
|
||
}
|
||
}
|
||
}
|
||
}
|