1636 lines
74 KiB
C#
1636 lines
74 KiB
C#
//using BasicData;
|
||
//using BizDataAccess;
|
||
//using LitJson;
|
||
using BasicData;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.Data.SqlClient;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Runtime.Serialization.Json;
|
||
using System.Text;
|
||
using System.Text.RegularExpressions;
|
||
using System.Threading.Tasks;
|
||
using System.Web.Script.Serialization;
|
||
|
||
namespace DataLinkMesWork
|
||
{
|
||
|
||
|
||
|
||
public partial class DataLink
|
||
{
|
||
|
||
|
||
/// <summary>
|
||
/// 1、执行存储过程，存储过程返回查询结果表，表以JSON字符串返回给页面。
|
||
/// 输入参数：参数名称1&参数&参数类型|参数名称1&参数&参数类型
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type1_Bakup(jsonobj json)
|
||
{
|
||
string[] parmas;
|
||
string[] names;
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK;
|
||
bool isOutput = false;
|
||
DataSet ds = null;
|
||
DataTable dt1 = null;
|
||
try
|
||
{
|
||
names = json.Name.Split('&');
|
||
string name;
|
||
name = names[0];
|
||
if (json.Param == "" || json.Param == null)//执行没有参数的存储过程
|
||
{
|
||
|
||
isOK = SQLCommon.ExecuteStoredProcedure(name, connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
|
||
parmas = json.Param.Split('&');
|
||
thisParms = new SqlParameter[parmas.Length];
|
||
SQLCommon.GetCmdParam(json, ref thisParms, out isOutput);
|
||
isOK = SQLCommon.ExecuteStoredProcedure(name, connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 1、执行存储过程，存储过程返回查询结果表，表以JSON字符串返回给页面。
|
||
/// 输入参数：参数名称1&参数&参数类型|参数名称1&参数&参数类型
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type1_Bakup(jsonobj json,out DataTable dt)
|
||
{
|
||
string[] parmas;
|
||
string[] names;
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK;
|
||
bool isOutput = false;
|
||
dt = new DataTable();
|
||
DataSet ds = null;
|
||
DataTable dt1 = null;
|
||
try
|
||
{
|
||
names = json.Name.Split('&');
|
||
string name;
|
||
name = names[0];
|
||
if (json.Param == "" || json.Param == null)//执行没有参数的存储过程
|
||
{
|
||
|
||
isOK = SQLCommon.ExecuteStoredProcedure(name, connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
|
||
parmas = json.Param.Split('&');
|
||
thisParms = new SqlParameter[parmas.Length];
|
||
SQLCommon.GetCmdParam(json, ref thisParms, out isOutput);
|
||
isOK = SQLCommon.ExecuteStoredProcedure(name, connectionStringBakup, ref thisParms, out ds, out result);
|
||
if (ds != null && ds.Tables.Count > 0)
|
||
{
|
||
dt = ds.Tables[0];
|
||
}
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// type = 2 Insert
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type2_Bakup(jsonobj json)
|
||
{
|
||
string[] parmas;
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK;
|
||
bool isOutput = false;
|
||
try
|
||
{
|
||
if (json.Param == "" || json.Param == null)//执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(json.Name, connectionStringBakup, ref thisParms, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
parmas = json.Param.Split('&');
|
||
thisParms = new SqlParameter[parmas.Length];
|
||
SQLCommon.GetCmdParam(json, ref thisParms, out isOutput);
|
||
isOK = SQLCommon.ExecuteStoredProcedure(json.Name, connectionStringBakup, ref thisParms, out result);
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"1\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// Type = 3
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type3_Bakup(jsonobj json)
|
||
{
|
||
string result = "[{ \"result\":\"0\"}]";
|
||
bool isOK;
|
||
if (json.Name != null)
|
||
{
|
||
DataTable dt = null;
|
||
try
|
||
{
|
||
isOK = SQLCommon.ExecuteDataTable(json.Name, connectionStringBakup, out dt, out result);
|
||
if (isOK)
|
||
{
|
||
if (dt != null)
|
||
{
|
||
result = JsonHelper.DataTableToJson(dt);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// Type = 3
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type3_Bakup(jsonobj json,out DataTable dt)
|
||
{
|
||
string result = "[{ \"result\":\"0\"}]";
|
||
bool isOK;
|
||
dt = new DataTable();
|
||
if (json.Name != null)
|
||
{
|
||
try
|
||
{
|
||
isOK = SQLCommon.ExecuteDataTable(json.Name, connectionStringBakup, out dt, out result);
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// type = 4
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type4_Bakup(jsonobj json)
|
||
{
|
||
string result = "[{ \"result\":\"0\"}]"; ;
|
||
bool isOK;
|
||
if (json.Name != null)
|
||
{
|
||
try
|
||
{
|
||
isOK = SQLCommon.ExecuteNonQuery(json.Name, connectionStringBakup, out result);
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"1\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
result = "[{ \"result\":\"0\"}]";
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// type=5
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type5_Bakup(jsonobj json)
|
||
{
|
||
string result = "";
|
||
DataSet ds = null;
|
||
try
|
||
{
|
||
|
||
if (json.Param == "" || json.Param == null)//执行没有参数的存储过程
|
||
{
|
||
SQLCommon.ExecuteStoredProcedure(json.Name, connectionStringBakup, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
string[] parmas;
|
||
SqlParameter[] thisParms;
|
||
try
|
||
{
|
||
parmas = json.Param.Split('|');
|
||
thisParms = new SqlParameter[parmas.Length];
|
||
for (int i = 0; i < parmas.Length; i++)
|
||
{
|
||
string[] pp = parmas[i].Split('&');
|
||
object inputValue = null;
|
||
if (pp.Length == 3)
|
||
{
|
||
switch (pp[2])
|
||
{
|
||
case "Int":
|
||
inputValue = Convert.ToInt32(pp[1]);
|
||
break;
|
||
case "String":
|
||
inputValue = pp[2];
|
||
break;
|
||
case "Boolean":
|
||
inputValue = Convert.ToBoolean(pp[1]);
|
||
break;
|
||
case "DateTime":
|
||
inputValue = Convert.ToDateTime(pp[1]);
|
||
break;
|
||
default:
|
||
inputValue = pp[1];
|
||
break;
|
||
}
|
||
thisParms[i] = new SqlParameter(pp[0], inputValue);
|
||
}
|
||
else if (pp.Length == 2)
|
||
{
|
||
thisParms[i] = new SqlParameter(pp[0], pp[1]);
|
||
}
|
||
|
||
}
|
||
}
|
||
catch (Exception)
|
||
{
|
||
return result = "参数有误,请检查参数的格式是否正确";
|
||
}
|
||
SQLCommon.ExecuteStoredProcedure(json.Name, connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
|
||
if (ds != null && ds.Tables.Count > 0)
|
||
{
|
||
result = "[{ \"result\":\"1\"}]";
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
result = ex.Message;
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 7 vue格式Excel 导入到创建并表中
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type7_Bakup(JsonData json)
|
||
{
|
||
string result = "";
|
||
bool isOK = false;
|
||
JsonData jsonDataName;
|
||
JsonData jsonDataParam;
|
||
JsonData jsonDataParamRow;
|
||
string name;
|
||
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
JsonData param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData_Type7(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
|
||
name = jsonDataName.ToString();
|
||
|
||
using (SqlConnection conn = new SqlConnection(connectionStringBakup))
|
||
{
|
||
try
|
||
{
|
||
conn.Open();
|
||
using (SqlCommand cmd = new SqlCommand(name, conn))
|
||
{
|
||
cmd.CommandTimeout = 0;
|
||
cmd.CommandType = CommandType.Text;
|
||
#region 首先删除并重建表
|
||
string createTableStr_key1 = "";
|
||
foreach (KeyValuePair<string, JsonData> jParamData in jsonDataParam[0])
|
||
{
|
||
createTableStr_key1 = createTableStr_key1 + "[" + jParamData.Key + "]" + " nvarchar(max),";
|
||
}
|
||
createTableStr_key1 = createTableStr_key1.TrimEnd(',');
|
||
//判断表是否存在，存在则删除重建，不存在直接重建
|
||
string dropsql = " if exists(select * from sysobjects where id = object_id(N'[" + name + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" +
|
||
" begin "
|
||
+ " DROP TABLE " + name
|
||
+ " CREATE TABLE " + name
|
||
+ "(" + " TableId int identity(1,1), " + createTableStr_key1 + ")"
|
||
+ " end "
|
||
+ " else "
|
||
+ " begin "
|
||
+ " CREATE TABLE " + name
|
||
+ "(" + " TableId int identity(1,1), " + createTableStr_key1 + ")"
|
||
+ " end ";
|
||
//DataAccess.ExecuteSQL(dropsql);
|
||
cmd.CommandText = dropsql;
|
||
cmd.ExecuteNonQuery();
|
||
#endregion
|
||
#region 循环插入数据
|
||
|
||
for (int i = 0; i < jsonDataParam.Count; i++)
|
||
{
|
||
jsonDataParamRow = jsonDataParam[i];
|
||
string sqlInert;//sql字符串
|
||
string createTableStr_key = null;//列
|
||
string createTableStr_value = null;//值
|
||
sqlInert = "INSERT INTO " + name + "(";
|
||
foreach (KeyValuePair<string, JsonData> jParamData in jsonDataParamRow)
|
||
{
|
||
createTableStr_key = createTableStr_key + "[" + jParamData.Key + "]" + ",";
|
||
createTableStr_value = createTableStr_value + "'" + jParamData.Value.ToString().Replace("'", "''") + "'" + ",";
|
||
}
|
||
createTableStr_key = createTableStr_key.TrimEnd(',');
|
||
createTableStr_value = createTableStr_value.TrimEnd(',');
|
||
sqlInert += createTableStr_key + ")" + "values" + "(";
|
||
sqlInert += createTableStr_value + ")";//最终sql字符串
|
||
cmd.CommandText = sqlInert;
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
#endregion
|
||
}
|
||
isOK = true;
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
// errorMessage = e.ToString();
|
||
}
|
||
finally
|
||
{
|
||
if (conn.State == ConnectionState.Open)
|
||
{
|
||
conn.Close();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"1\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
|
||
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 1001
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExecuteInsertMesWork_Bakup(JsonData json)
|
||
{
|
||
SqlParameter thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;
|
||
bool isOutput = false;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称 Sql
|
||
JsonData jsonDataParam;//存储过程参数
|
||
long outputID = 0;
|
||
SqlParameter[] param = null;
|
||
string err;
|
||
List<SqlParameter> ilistSqlParameterStr = new List<SqlParameter>();
|
||
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转为JSON格式
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteInsertMesWork(jsonDataName.ToString(), ref param, connectionStringBakup, out err);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
//thisParms = new []SqlParameter();
|
||
//thisParms = new SqlParameter[jsonData.Count];
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
if (name == "output")
|
||
{
|
||
isOutput = true;
|
||
}
|
||
else
|
||
{
|
||
thisParms = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
if(jsonData[i]["output"]!=null)
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
if(jsonData[i]["type"] != null)
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms = new SqlParameter(name, inputValue);
|
||
}
|
||
}
|
||
if (output == "1")
|
||
{
|
||
|
||
thisParms.Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
ilistSqlParameterStr.Add(thisParms);
|
||
}
|
||
|
||
}
|
||
if (ilistSqlParameterStr.Count > 0)
|
||
{
|
||
param = ilistSqlParameterStr.ToArray();
|
||
}
|
||
if (isOutput)
|
||
{
|
||
|
||
isOK = SQLCommon.ExecuteInsertMesWork(jsonDataName.ToString(), ref param, connectionStringBakup, out outputID,out err);
|
||
}
|
||
else
|
||
{
|
||
isOK = SQLCommon.ExecuteInsertMesWork(jsonDataName.ToString(), ref param, connectionStringBakup, out err);
|
||
}
|
||
}
|
||
}
|
||
if (isOK)
|
||
{
|
||
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
}
|
||
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 1002
|
||
/// select * from Test where f1=@f1
|
||
/// [{\"name\":\"@f1\",\"value\":\"T002\"}]
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// {"type":"1002","name":"select * from Test where f1=@f1 ","param":"[{\"name\":\"@f1\",\"value\":\"T002\"}]"}
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExecuteSelectMesWork_Bakup(JsonData json)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
DataTable dt = null;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称 Sql
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
string err;
|
||
isOK = SQLCommon.ExecuteSelectMesWork(jsonDataName.ToString(), ref thisParms, connectionStringBakup, out dt,out err);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
if(jsonData[i]["output"] != null)
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
if(jsonData[i]["type"] != null)
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, value);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
}
|
||
|
||
}
|
||
if (output == "1")
|
||
{
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
string err;
|
||
isOK = SQLCommon.ExecuteSelectMesWork(jsonDataName.ToString(), ref thisParms, connectionStringBakup, out dt,out err);
|
||
|
||
}
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <param name="dt"></param>
|
||
/// <returns></returns>
|
||
public static string ExecuteSelectMesWork_Bakup(JsonData json,out DataTable dt)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
dt = new DataTable();
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称 Sql
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
string err;
|
||
isOK = SQLCommon.ExecuteSelectMesWork(jsonDataName.ToString(), ref thisParms, connectionStringBakup, out dt,out err);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output") )
|
||
{
|
||
if(jsonData[i]["output"] != null)
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
if(jsonData[i]["type"] != null)
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
object inputValue = GetInputValue(type, value);
|
||
thisParms[i].Value = inputValue;
|
||
}
|
||
}
|
||
if (output == "1")
|
||
{
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
string err;
|
||
isOK = SQLCommon.ExecuteSelectMesWork(jsonDataName.ToString(), ref thisParms, connectionStringBakup, out dt,out err);
|
||
|
||
}
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 11 存储过程方式查询
|
||
/// 查询 采用新协议 例:{"type":"11","name":"存储过程","param":"[{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]"}
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type11_Bakup(JsonData json)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
DataSet ds = null;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
}
|
||
if (output == "1")
|
||
{
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
|
||
}
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 11 存储过程方式查询
|
||
/// 查询 采用新协议 例:{"type":"11","name":"存储过程","param":"[{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]"}
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type13_Bakup(JsonData json)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
DataSet ds = null;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
if (jsonDataParam == null)
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
|
||
}
|
||
else
|
||
{
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
}
|
||
if (output == "1")
|
||
{
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
}
|
||
|
||
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
public static string ExePROCEDURE_Type15_Bakup(JsonData json,string filename,string suffix,byte[]bytes)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
DataSet ds = null;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
if (jsonDataParam == null)
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
|
||
}
|
||
else
|
||
{
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
}
|
||
if (output == "1")
|
||
{
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
thisParms[thisParms.Length - 3].Value = filename;
|
||
thisParms[thisParms.Length - 2].Value = suffix;
|
||
thisParms[thisParms.Length - 1].Value = bytes;
|
||
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
}
|
||
|
||
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
public static string ExePROCEDURE_Type16_Bakup(JsonData json,out byte[]bytes, out string fileName, out string suffix)
|
||
{
|
||
bytes = null;
|
||
fileName = "";
|
||
suffix = "";
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
DataSet ds = null;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
if (jsonDataParam == null)
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
|
||
}
|
||
else
|
||
{
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
}
|
||
if (output == "1")
|
||
{
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
|
||
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
}
|
||
}
|
||
if (isOK)
|
||
{
|
||
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 分页查询
|
||
/// "UserID": $("#UserID").val(),
|
||
/// "Type": $("#TYPE").val(),
|
||
/// "Name": $("#NAME").val(),
|
||
/// "Param": $("#PARAM1value").val(),
|
||
/// "Pagination": $("#Pagination").val(),
|
||
/// HasReturn: $("#HASRETURN").get(0).checked
|
||
/// </summary>
|
||
/// <param name="tbname">要分页显示的表名</param>
|
||
/// <param name="fieldkey">用于定位记录的主键(惟一键)字段,只能是单个字段</param>
|
||
/// <param name="where">查询条件</param>
|
||
/// <param name="fieldshow">以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段</param>
|
||
/// <param name="fieldOrder">以逗号分隔的排序字段列表,可以指定在字段后面指定DESC / ASC--用于指定排序顺序</param>
|
||
/// <param name="PageCurrent">要显示的页码</param>
|
||
/// <param name="PageSizeint">每页的大小(记录数)</param>
|
||
/// <param name="dt">返回的结果表</param>
|
||
/// <param name="PageCount">总页数</param>
|
||
/// <param name="itemCount">数据总数</param>
|
||
public static bool ExePROCEDURE_Type1003_Bakup(string tbname,string fieldkey,string where,string fieldshow,string fieldorder,int pagecurrent,int pagesize ,
|
||
out DataTable dt,out int pagecount,out int itemcount)
|
||
{
|
||
dt = new DataTable();
|
||
DataSet ds;
|
||
itemcount = 0;
|
||
string ErrorMessage;
|
||
return DataAccess.ExecPageQuery(tbname, connectionStringBakup, fieldkey, where, fieldshow, fieldorder, pagecurrent, pagesize, out pagecount, out itemcount, out ds, out ErrorMessage);
|
||
}
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <param name="dt"></param>
|
||
/// <param name="pagecount"></param>
|
||
/// <param name="itemcount"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type1003_Bakup(JsonData json, out DataTable dt, out int pagecount,out int itemcount)
|
||
{
|
||
string tbname="";
|
||
string fieldkey="";
|
||
string where="";
|
||
string fieldshow="";
|
||
string fieldorder="";
|
||
int pagecurrent=1;
|
||
int pagesize=15;
|
||
pagecount=0;
|
||
dt = new DataTable();
|
||
|
||
DataSet ds;
|
||
itemcount = 0;
|
||
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
dt = new DataTable();
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
if(jsonData.Count>0)
|
||
{
|
||
if (jsonData[0]["tbname"] != null)
|
||
{
|
||
tbname = jsonData[0]["tbname"].ToString();
|
||
}
|
||
if (jsonData[0]["fieldkey"] != null)
|
||
{
|
||
fieldkey = jsonData[0]["fieldkey"].ToString();
|
||
}
|
||
if (jsonData[0]["where"] != null)
|
||
{
|
||
where = jsonData[0]["where"].ToString();
|
||
}
|
||
if (jsonData[0]["fieldshow"] != null)
|
||
{
|
||
fieldshow = jsonData[0]["fieldshow"].ToString();
|
||
}
|
||
if (jsonData[0]["fieldorder"] != null)
|
||
{
|
||
fieldorder = jsonData[0]["fieldorder"].ToString();
|
||
}
|
||
if (jsonData[0]["pagecurrent"] != null)
|
||
{
|
||
pagecurrent = Convert.ToInt32(jsonData[0]["pagecurrent"]);
|
||
}
|
||
if (jsonData[0]["pagesize"] != null)
|
||
{
|
||
pagesize = Convert.ToInt32(jsonData[0]["pagesize"]);
|
||
}
|
||
}
|
||
|
||
isOK = ExePROCEDURE_Type1003_Bakup(tbname, fieldkey, where, fieldshow, fieldorder, pagecurrent, pagesize, out dt, out pagecount, out itemcount);
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public static string ExePROCEDURE_Type11_Bakup(JsonData json,out DataTable dt)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;// 存储过程执行是否成功
|
||
bool isOutput = false;
|
||
DataSet ds = null;
|
||
dt = new DataTable();
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转格式
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//参数为空，执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
thisParms = new SqlParameter[jsonData.Count];//定义参数个数
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
//string value = jsonData[i]["value"].ToString();// 参数值
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if (jsonData[i].ContainsKey("output"))
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
if (jsonData[i].ContainsKey("type"))
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
}
|
||
if (output == "1")
|
||
{
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out ds, out result);
|
||
if (ds != null && ds.Tables.Count > 0)
|
||
{
|
||
dt = ds.Tables[0];
|
||
}
|
||
|
||
}
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 12
|
||
/// 增删改 DbCallType2 的更新版 采用新协议 {"type":"12","name":"存储过程","param":"[{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]"}
|
||
/// </summary>
|
||
/// <param name="json"></param>
|
||
/// <returns></returns>
|
||
public static string ExePROCEDURE_Type12_Bakup(JsonData json)
|
||
{
|
||
SqlParameter[] thisParms = null;
|
||
string result = "";
|
||
bool isOK = false;
|
||
bool isOutput = false;
|
||
JsonData jsonData;
|
||
JsonData jsonDataName;//存储过程名称
|
||
JsonData jsonDataParam;//存储过程参数
|
||
try
|
||
{
|
||
{
|
||
|
||
string type1;
|
||
string name1;
|
||
string param1;
|
||
string userID1;
|
||
string pagination1;
|
||
bool hasReturn1;
|
||
string modularID1;
|
||
GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);
|
||
|
||
jsonDataName = name1;//存储过程名称
|
||
jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
|
||
jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转为JSON格式
|
||
if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//执行没有参数的存储过程
|
||
{
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out result);
|
||
}
|
||
else//执行有参数的存储过程
|
||
{
|
||
thisParms = new SqlParameter[jsonData.Count];
|
||
for (int i = 0; i < jsonData.Count; i++)
|
||
{
|
||
|
||
string name = jsonData[i]["name"].ToString();// 参数名
|
||
string value = null;
|
||
try
|
||
{
|
||
//判断是否为数组，若为数组转为字符串格式
|
||
if (jsonData[i]["value"].IsArray == true)
|
||
{
|
||
//数组转字符串
|
||
value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
|
||
// unicode解码
|
||
value = DeUnicode(value);
|
||
}
|
||
else//不是数组格式直接转字符串
|
||
{
|
||
value = jsonData[i]["value"].ToString();// 参数值
|
||
}
|
||
|
||
}
|
||
catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
|
||
{
|
||
value = null;
|
||
}
|
||
string output = null;// 参数是否为输出类型
|
||
string type = null;// 参数类型
|
||
thisParms[i] = new SqlParameter(name, value);
|
||
if(jsonData[i].ContainsKey("output"))
|
||
{
|
||
if(jsonData[i]["output"] != null)
|
||
{
|
||
output = jsonData[i]["output"].ToString();// 参数是否为输出类型
|
||
}
|
||
}
|
||
if(jsonData[i].ContainsKey("type"))
|
||
{
|
||
if(jsonData[i]["type"] != null)
|
||
{
|
||
type = jsonData[i]["type"].ToString();// 参数类型
|
||
}
|
||
}
|
||
|
||
if (output == "1")
|
||
{
|
||
object inputValue = null; ;
|
||
inputValue = GetInputValue(type, output);
|
||
thisParms[i] = new SqlParameter(name, inputValue);
|
||
thisParms[i].Direction = ParameterDirection.Output;
|
||
isOutput = true;// 存在输出参数
|
||
}
|
||
}
|
||
isOK = SQLCommon.ExecuteStoredProcedure(jsonDataName.ToString(), connectionStringBakup, ref thisParms, out result);
|
||
}
|
||
|
||
}
|
||
if (isOK)
|
||
{
|
||
result = "[{ \"result\":\"" + "1" + "\"}]";
|
||
|
||
}
|
||
|
||
else
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
result = "[{ \"result\":\"" + "0" + "\"}]";
|
||
}
|
||
return result;
|
||
}
|
||
}
|
||
}
|