/// <summary>
/// 数据库参数转json
/// </summary>
/// <param name="Pa"></param>
/// <returns></returns>
public static string GysoftParameterToJson(GysoftParameter[] Pa)
{
if (Pa == null)
{
return "";
}
StringBuilder Json = new StringBuilder();
Json.Append("[");
for (int i = 0; i < Pa.Length; i++)
{
Json.Append("{");
Json.Append(is_charmarks + "name" + is_charmarks + ":" + is_charmarks +Pa[i].PaName+is_charmarks+",");
Json.Append(is_charmarks + "value" + is_charmarks + ":" + is_charmarks + Pa[i].Value + is_charmarks + ",");
Json.Append(is_charmarks + "Oper" + is_charmarks + ":" + is_charmarks + Pa[i].Oper + is_charmarks);
Json.Append("}");
//最后一个不需要加,号了
if (i < Pa.Length -1)
{
Json.Append(",");
}
}
Json.Append("]");
return Json.ToString();
}
public override char GetPaNameSign() { return '@'; } //获取传入参数符号
//生成post所需要的字符串
internal string vf_En_Str(string as_sql,string as_action, GysoftParameter[] Pa=null)
{
string ls_date;
ls_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string ls_key;
ls_key = vf_GetKey(ls_date);
string ls_str;
ls_str = DES.of_EncryStr_Gy(HttpPostStr, ls_key);
//加密sql
as_sql = DES.of_EncryStr_Gy(as_sql, ls_key);
//提交参数
string strMessage;
strMessage = "action=" + as_action + "&str=" + ls_str + "&date=" +GYstring.UrlEncode(ls_date) +
"&driverno=" + SqlHelper.HttpPost_driverNO +
"&comname=" + SqlHelper.HttpPost_ComName+
"&sql="+as_sql+"&pagesize="+SqlHelper.HttpPost_pagesize.ToString()+
"&begin="+SqlHelper.HttpPost_begin.ToString()+"&keyname="+SqlHelper.HttpPost_keyname;
if (Pa != null)
{
string ls_Pa;
ls_Pa = Json.GysoftParameterToJson(Pa);
ls_Pa = DES.of_EncryStr_Gy(ls_Pa, ls_key);
strMessage = strMessage + "&dbparam=" + ls_Pa;
}
return strMessage;
}
public override char GetPaNameSign() { return '@'; } //获取传入参数符号
//执行Sql语句，返回Int
public override int ExecuteNonQuery(CommandType cmdType, string strSql, GysoftParameter[] Pa)
{
ClearErr();
//暂未处理pa
string ls_str;
ls_str =YW.of_En_Str(strSql,"of_executenonquery",Pa);
string ls_Rel = YW.SendMessage_SQL(ls_str);
if (GYstring.of_LeftStr(ls_Rel,2).ToUpper() != "OK")
{
ErrStr = ls_Rel;
return 0;
}
ls_Rel = GYstring.of_MidStr(ls_Rel,2);
if (GYstring.of_LeftStr(ls_Rel, 1) == ",")
{
ls_Rel = GYstring.of_MidStr(ls_Rel, 1); //去掉,号
}
try
{
int li_Rel = Convert.ToInt32(ls_Rel);
return li_Rel;
}
catch
{
ErrStr = "无法转换成Int,Rel=" + ls_Rel;
return 0;
}
}
public int vf_GetParameter(out GysoftParameter[] aGyPa_Rel)
{
aGyPa_Rel = new GysoftParameter[iDic_ValuePa.Count];
int li_Num = 0;
foreach (GysoftParameter Pa in iDic_ValuePa.Values)
{
aGyPa_Rel[li_Num] = Pa;
li_Num++;
}
return li_Num;
}
/// <summary>
/// 返回名称
/// </summary>
private string vf_AddValuePa(string as_Name, object aobj_Value)
{
//清除特殊字符只保留英文和数字
//as_Name = Regex.Replace(as_Name, "[^A-Za-z0-9]", "");
//加个结束标记
as_Name += ics_EndWork;
//加入参数
if (iDic_ValuePa.ContainsKey(as_Name))
{ iDic_ValuePa[as_Name] = new GysoftParameter("@" + as_Name, aobj_Value); }
else
{ iDic_ValuePa.Add(as_Name, new GysoftParameter("@" + as_Name, aobj_Value)); }
return as_Name;
}
//执行Sql，返回DataTable
public override DataTable ExecuteDataTable(CommandType cmdType, string strSql, GysoftParameter[] Pa)
{
ClearErr();
string ls_date = gydate.of_GetNowStr();
//暂未处理pa
string ls_str;
ls_str = YW.of_En_Str(strSql, "of_executedatatable",ls_date,Pa);
string ls_Rel = YW.SendMessage_SQL(ls_str);
if (GYstring.of_LeftStr(ls_Rel, 2).ToUpper() != "OK")
{
ErrStr = ls_Rel;
return new DataTable();
}
return YW.of_Http_Unzip_unDES(ls_Rel, ls_date,"");
}
public abstract int ExecuteScalarNum(string strSql, GysoftParameter[] Pa);
public abstract string ExecuteScalar(string strSql, GysoftParameter[] Pa);
//执行Sql，返回int public abstract int ExecuteScalarNum(CommandType cmdType, string strSql, GysoftParameter[] Pa);
//执行Sql，返回DataTable public abstract DataTable ExecuteDataTable(CommandType cmdType, string strSql, GysoftParameter[] Pa);
public abstract DataTable ExecuteDataTable(string strSql, GysoftParameter[] Pa);
public string of_GetUpdateSQL(out GysoftParameter[] aGyPa_Rel)
{
int i = 0;
string ls_SQL = null;
string ls_Set = "";
i = 0;
while (i < ii_count)
{
string ls_OneValue = null;
ls_OneValue = isa_cols[i].is_value;
if (ls_OneValue == null)
ls_OneValue = "";
ls_OneValue = ls_OneValue.Trim();
// update 表名 set dbfield=.....
GYstring.gf_addstring_list(ref ls_Set, isa_cols[i].is_DbField + "=@" + isa_cols[i].is_DbField + ics_EndWork, ",");
i = i + 1;
}
if (!string.IsNullOrEmpty(is_where))
{
ls_SQL = "update " + is_TableName + " set " + ls_Set + " where " + is_where;
}
else
{
ls_SQL = "update " + is_TableName + " set " + ls_Set ;
}
//对应的参数
vf_GetParameter(out aGyPa_Rel);
return ls_SQL;
}
public long ExecuteScalarLong(GysoftParameter[] Pa)
{
GysoftParameter[] lv_Pa; //这个参数没有意义
string ls_Sql = vf_GetSQLselect(out lv_Pa);
long ll_rc;
ErrorStr = "";
ll_rc=GyConvert.of_ToInt64(SqlHelper.ExecuteScalar(ls_Sql, Pa));
ErrorStr = SqlHelper.ErrStr;
return ll_rc;
}
public int of_execute(string as_where, string as_param1 = "", string as_param2 = "", string as_param3 = "", string as_param4 = "", string as_param5 = "")
{
if (as_param1 == "")
{
return 0;
}
if (GYstring.of_length(as_where) == 0)
{
return 0;
}
if (GYstring.of_LeftStr(as_where, 1) == "@")
{
ErrStr = "of_execute的第一个参数，为where条件，不能是@开头";
return -1;
}
int li_len = 0;
if (as_param1.IndexOf("@") >= 0)
{
if (as_param1.IndexOf("=") < 0)
{
ErrStr = "as_param作为参数，必需有@和=";
return -1;
}
li_len++;
}
if (as_param2.IndexOf("@") >= 0)
{
if(as_param2.IndexOf("=") < 0)
{
ErrStr = "as_param作为参数，必需有@和=";
return -1;
}
li_len++;
}
if (as_param3.IndexOf("@") >= 0)
{
if (as_param3.IndexOf("=") < 0)
{
ErrStr = "as_param作为参数，必需有@和=";
return -1;
}
li_len++;
}
if (as_param4.IndexOf("@") >= 0)
{
if (as_param3.IndexOf("=") < 0)
{
ErrStr = "as_param作为参数，必需有@和=";
return -1;
}
li_len++;
}
if (as_param5.IndexOf("@") >= 0)
{
if (as_param3.IndexOf("=") < 0)
{
ErrStr = "as_param作为参数，必需有@和=";
return -1;
}
li_len++;
}
//开始赋值
string ls_paramName, ls_value, ls_str;
int li_pos;
GysoftParameter[] pa_guid = new GysoftParameter[li_len];
int li_row = 0;
if ((as_param1.IndexOf("@") >= 0) && (as_param1.IndexOf("=") > 0))
{
ls_str = as_param1;
li_pos = ls_str.IndexOf("=");
ls_paramName = GYstring.of_LeftStr(ls_str, li_pos);
ls_value = GYstring.of_MidStr(ls_str, li_pos + 1);
pa_guid[li_row] = new GysoftParameter(ls_paramName, ls_value);
//pa_guid[li_row].PaName = ls_paramName.Trim();
//pa_guid[li_row].Value = ls_value.Trim();
li_row++;
}
if ((as_param2.IndexOf("@") >= 0) && (as_param2.IndexOf("=") > 0))
{
ls_str = as_param2;
li_pos = ls_str.IndexOf("=");
ls_paramName = GYstring.of_LeftStr(ls_str, li_pos);
ls_value = GYstring.of_MidStr(ls_str, li_pos + 1);
pa_guid[li_row] = new GysoftParameter(ls_paramName, ls_value);
li_row++;
}
if ((as_param3.IndexOf("@") >= 0) && (as_param3.IndexOf("=") > 0))
{
ls_str = as_param3;
li_pos = ls_str.IndexOf("=");
ls_paramName = GYstring.of_LeftStr(ls_str, li_pos);
ls_value = GYstring.of_MidStr(ls_str, li_pos + 1);
pa_guid[li_row] = new GysoftParameter(ls_paramName, ls_value);
li_row++;
}
if ((as_param4.IndexOf("@") >= 0) && (as_param4.IndexOf("=") > 0))
{
ls_str = as_param4;
li_pos = ls_str.IndexOf("=");
ls_paramName = GYstring.of_LeftStr(ls_str, li_pos);
ls_value = GYstring.of_MidStr(ls_str, li_pos + 1);
pa_guid[li_row] = new GysoftParameter(ls_paramName, ls_value);
li_row++;
}
if ((as_param5.IndexOf("@") >= 0) && (as_param5.IndexOf("=") > 0))
{
ls_str = as_param5;
li_pos = ls_str.IndexOf("=");
ls_paramName = GYstring.of_LeftStr(ls_str, li_pos);
ls_value = GYstring.of_MidStr(ls_str, li_pos + 1);
pa_guid[li_row] = new GysoftParameter(ls_paramName, ls_value);
li_row++;
}
return of_execute(as_where, pa_guid);
}
//撤销GysoftParameter,将参数加入SQL语句中
private string RepealPa(string as_SQL, GysoftParameter[] aPa)
{
foreach (GysoftParameter Pa_Temp in aPa)
{
as_SQL = as_SQL.Replace(Pa_Temp.PaName, "'" + GYstring.SQLEncode(Pa_Temp.Value.ToString()) + "'");
}
return as_SQL;
}
//生成post所需要的字符串
internal static string of_En_Str(string as_sql, string as_action,string as_date, GysoftParameter[] Pa = null)
{
return of_En_Str_SQL(as_sql, as_action, as_date, null, Pa);
}
internal static string of_En_Str(string as_sql, string as_action, GysoftParameter[] Pa = null)
{
string ls_date;
ls_date = gydate.of_GetNowStr();
return of_En_Str(as_sql, as_action,ls_date, Pa);
}
/// <summary>
/// 加密SQL语句
/// </summary>
/// <param name="as_sql"></param>
/// <param name="as_action"></param>
/// <param name="as_date"></param>
/// <param name="ldict_Str"></param>
/// <param name="Pa"></param>
/// <returns></returns>
internal static string of_En_Str_SQL(string as_sql, string as_action, string as_date,Dictionary<string,string> ldict_Str, GysoftParameter[] Pa = null)
{
string ls_key;
ls_key = vf_GetKey(as_date);
//加密sql
as_sql = DES.of_EncryStr_Gy(as_sql, ls_key);
//提交参数
string strMessage;
//201508版
strMessage = "action=" + as_action + YW.of_GetAddParam_1508(as_date) +
"&sql=" + as_sql + SqlHelper.HttpParamStr + "&ZIP=" + ZipType+"&isEn=Y";
StringBuilder lnv_add = new StringBuilder();
if (ldict_Str != null)
{
foreach(string key in ldict_Str.Keys)
{
string ls_value;
ls_value=ldict_Str[key];
lnv_add.Append("&"+key+DES.of_EncryStr_Gy(ls_value, ls_key));
}
strMessage = strMessage + lnv_add.ToString();
}
if (Pa != null)
{
string ls_Pa;
ls_Pa = Json.GysoftParameterToJson(Pa);
ls_Pa = DES.of_EncryStr_Gy(ls_Pa, ls_key);
strMessage = strMessage + "&dbparam=" + ls_Pa;
}
//支付http访问分页功能 2015.08.06
if (SqlHelper.HttpPost_IsPage)
{
strMessage = strMessage + "&ispage=Y";
}
return strMessage;
}
public DataTable Of_GetPageDataTable(string as_keyName, int ai_PageSize, int ai_PageIndex, ref int ai_totalrow,GysoftParameter[] ap_list)
{
ErrorStr = "";
if (string.IsNullOrEmpty(this.SQL))
{
ErrorStr = " Of_GetPageDataTable 取this.sql为空：" + as_keyName;
//Log.WriteTextLog("n_findby_dw", "error", " Of_GetPageDataTable 取this.sql为空：" + as_keyName);
return null;
}
n_form_where lnv_part=new n_form_where();
lnv_part.of_SetSQL(this.SQL);
string ls_key_md5, ls_json;
ls_key_md5 = DES.of_Md5(this.SQL);
string ls_Sql;
//取到前面多少条记录
int li_top = 0;
li_top = ai_PageSize * ai_PageIndex;
if ((ai_PageIndex == 1) && (ai_totalrow >= 0))
{
li_top = 20000; //最大只取到2万行
}
//只取主键列的数据 ，目前假设主键列，只有一个字段
GysoftParameter[] Pa;
ls_Sql = this.vf_GetSQLselect(out Pa, as_keyName, li_top);
//查看一下是否缓存已经有了
DataTable ldt_Tb = new DataTable();
if (ap_list != null)
{
int li_paCount;
li_paCount = Pa.Length;
Array.Resize(ref Pa, li_paCount + ap_list.Length);
for (int li_p = 0; li_p < ap_list.Length; li_p++)
{
Pa[li_paCount + li_p] = new GysoftParameter(ap_list[li_p].PaName, ap_list[li_p].Value);
}
}
//没有缓存的数据
if (gydatatable.of_DataTableRowcount(ldt_Tb) <= 0)
{
ldt_Tb = SqlHelper.ExecuteDataTable(ls_Sql, Pa);
ai_totalrow = gydatatable.of_DataTableRowcount(ldt_Tb); //最大只取到2万行
ErrorStr = SqlHelper.ErrStr;
ls_json = Json.DataTableToJson(ldt_Tb);
}
//if (ldt_Tb != null)
//{
// li_count = ldt_Tb.Rows.Count; //没有记录
//}
string ls_InWhere = "";
if (ai_totalrow == 0)
{
ai_totalrow = 0;
inv_where.of_add_ons("findbytop", "0=1");
ls_Sql = inv_where.of_GetSQLselect();
ldt_Tb = SqlHelper.ExecuteDataTable(ls_Sql, Pa);
inv_where.of_del_ons("findbytop");
//返回空结果值
return ldt_Tb;
}
string ls_ColType = null;
if (ldt_Tb.Columns.Count == 1)
{
ls_ColType = ldt_Tb.Columns[0].DataType.Name.ToLower();
}
else
{
ls_ColType = "string"; //有多列
}
string ls_One = null;
string ls_In = null;
ls_In = "";
int li_row = 0;
//取到这一页的主键值
int li_Total = 0;
li_Total = ai_PageSize * ai_PageIndex;
if (ldt_Tb.Rows.Count < li_Total)
li_Total = ldt_Tb.Rows.Count;
for (li_row = ai_PageSize * (ai_PageIndex - 1); li_row <= li_Total - 1; li_row++)
{
ls_One = ldt_Tb.Rows[li_row][0].ToString().Trim();
//目前最多支持二列
if (ldt_Tb.Columns.Count > 1)
{
ls_One += "#" + ldt_Tb.Rows[li_row][1].ToString().Trim();
}
if (ls_ColType == "string")
{
ls_One = "'" + ls_One + "'";
}
GYstring.gf_addstring_list(ref ls_In, ls_One, ",");
//每5个值，换一行，好看点
if (li_row % 5 == 0)
{
ls_In += "\r\n";
}
}
invalue = ls_In;
if (ls_In.Trim().Length > 0)
{
if (ldt_Tb.Columns.Count > 1)
{
//多列用#联结起来
string ls_oneCol, ls_TwoCol;
int li_pos;
li_pos = as_keyName.IndexOf(",");
ls_oneCol = as_keyName.Substring(0, li_pos);
ls_TwoCol = as_keyName.Substring(li_pos + 1);
if (SqlHelper.Dbtype.ToString().ToLower() == "mssql")
{
as_keyName = "TRIM(" + ls_oneCol + ")+TRIM(" + ls_TwoCol + ")";
}
else //mysql版
{
as_keyName = "CONCAT(TRIM(" + ls_oneCol + "), CONCAT('#',TRIM(" + ls_TwoCol + "))) ";
}
ls_InWhere = as_keyName + " in (" + ls_In + ")";
}
ls_InWhere = as_keyName + " in (" + ls_In + ")";
}
else
{
ls_InWhere = "0=1";
}
//本次需要显示的记录对应的主键 id
inv_where.of_add_ons("findbytop", ls_InWhere);
ls_Sql = inv_where.of_GetSQLselect();
ErrorStr = "";
ldt_Tb = SqlHelper.ExecuteDataTable(ls_Sql, Pa);
ErrorStr = SqlHelper.ErrStr;
inv_where.of_del_ons("findbytop");
//显示全部的SQL
//__AllSQL = RepealPa(ls_Sql, Pa);
return ldt_Tb;
}
public DataTable ExecuteDataTable(GysoftParameter[] Pa)
{
DataTable ldt_tb = new DataTable();
string ls_Sql = vf_GetSQLselect(out Pa);
ErrorStr = "";
ldt_tb= SqlHelper.ExecuteDataTable(ls_Sql, Pa);
ErrorStr = SqlHelper.ErrStr;
return ldt_tb;
}
public int ExecuteScalarInt(GysoftParameter[] Pa)
{
GysoftParameter[] lv_Pa; //这个参数没有意义 ，以外面传入的为准
string ls_Sql = vf_GetSQLselect(out lv_Pa);
ErrorStr = "";
int li_rc;
li_rc = GyConvert.of_ToInt32(SqlHelper.ExecuteScalar(ls_Sql, Pa));
ErrorStr = SqlHelper.ErrStr;
return li_rc;
}
public decimal ExecuteScalarNum(GysoftParameter[] Pa)
{
GysoftParameter[] lv_Pa; //这个参数没有意义 ，以外面传入的为准
string ls_Sql = vf_GetSQLselect(out lv_Pa);
decimal ldc_num;
ErrorStr = "";
ldc_num=SqlHelper.ExecuteScalarNum(ls_Sql, Pa);
ErrorStr = SqlHelper.ErrStr;
return ldc_num;
}
/// <summary>
/// 取到SQL语句及相应的参数
/// </summary>
/// <param name="aGyPa_Rel"></param>
/// <returns></returns>
public string of_GetInsertSQL(out GysoftParameter[] aGyPa_Rel)
{
int i = 0;
string ls_SQL = null;
string ls_value = "";
string ls_insert = "";
i = 0;
while (i < ii_count)
{
string ls_OneValue = null;
ls_OneValue = isa_cols[i].is_value;
if (ls_OneValue == null)
{
i = i + 1;
continue;
}
ls_OneValue = ls_OneValue.Trim();
if (ls_OneValue.Length == 0)
{
i = i + 1;
continue;
}
GYstring.gf_addstring_list(ref ls_insert, isa_cols[i].is_DbField, ",");
GYstring.gf_addstring_list(ref ls_value, "@" + isa_cols[i].is_DbField+ics_EndWork, ",");
i = i + 1;
}
ls_SQL = " insert into " + is_TableName + "(" + ls_insert + ") values(" + ls_value + ")";
//对应的参数
vf_GetParameter(out aGyPa_Rel);
return ls_SQL;
}
public abstract int ExecuteNonQuery(string strSql, GysoftParameter[] Pa);
public int of_execute(string as_where ,GysoftParameter[] ay_param)
{
string ls_sql;
GysoftParameter[] aGyPa_Rel;
if (as_where == "")
{
ls_sql = of_GetInsertSQL(out aGyPa_Rel);
}
else
{
ls_sql = of_GetUpdateSQL(out aGyPa_Rel);
}
if (ls_sql.IndexOf(" where") > 0)
{
ls_sql += " and " + as_where;
}
else
{
ls_sql += " where " + as_where;
}
int li_len;
li_len = aGyPa_Rel.Length + ay_param.Length;
GysoftParameter[] ap_all = new GysoftParameter[li_len];
for (int li_i = 0; li_i < aGyPa_Rel.Length; li_i++)
{
ap_all[li_i] = aGyPa_Rel[li_i];
}
for (int li_i = 0; li_i < ay_param.Length; li_i++)
{
ap_all[aGyPa_Rel.Length + li_i] = ay_param[li_i];
}
int li_rc;
li_rc = SqlHelper.ExecuteNonQuery(ls_sql, ap_all);
ErrStr = SqlHelper.ErrStr;
//出错
return li_rc;
}
public string ExecuteScalar(GysoftParameter[] Pa)
{
GysoftParameter[] lv_Pa; //这个参数没有意义
string ls_Sql = vf_GetSQLselect(out lv_Pa);
string ls_num;
ls_num = SqlHelper.ExecuteScalar(ls_Sql, Pa);
ErrorStr = SqlHelper.ErrStr;
return ls_num;
}
public int of_executeSQL(string as_where, GysoftParameter[] ay_param)
{
return of_execute(as_where, ay_param);
}
public static GysoftParameter[] JsonToGysoftParameter(string strJson)
{
//取出表名
ErrStr = "";
GysoftParameter[] pa=null;
//获取数据
const string lsc_first = "[";
const string lsc_end = "]";
//格式不对，则直接跳出
if (GYstring.of_LeftStr(strJson, lsc_first.Length) != lsc_first)
{
ErrStr = "str first must is "+lsc_first;
return pa;
}
if (GYstring.of_RightStr(strJson, lsc_end.Length) != lsc_end)
{
ErrStr = "str end must is " + lsc_end;
return pa;
}
DataTable ldt_tb=new DataTable();
ldt_tb=Json.JsonToDataTable(strJson);
//创建字段
pa = new GysoftParameter[ldt_tb.Rows.Count];
for (int li_row = 0; li_row < pa.Length; li_row++)
{
pa[li_row] = new GysoftParameter();
pa[li_row].PaName = ldt_tb.Rows[li_row]["name"].ToString();
pa[li_row].Value = ldt_tb.Rows[li_row]["value"].ToString();
pa[li_row].Oper = ldt_tb.Rows[li_row]["oper"].ToString();
}
return pa;
}
/// <summary>
/// 更改Parameter格式
/// </summary>
/// <param name="al_pa"></param>
/// <returns></returns>
public static GysoftParameter[] ChangeParameter(List<GysoftParameter> al_pa)
{
GysoftParameter[] pa = new GysoftParameter[al_pa.Count];
for (int i = 0; i < al_pa.Count; i++)
{
pa[i] = al_pa[i];
}
return pa;
}
public abstract char GetPaNameSign(); //获取传入参数符号
//执行Sql语句，返回Int
public abstract int ExecuteNonQuery(CommandType cmdType, string strSql, GysoftParameter[] Pa);