执行update, insert,delete 语句, 不返回结果集,(类型化参数)
/// <summary> /// 执行update, insert,delete 语句, 不返回结果集,(类型化参数) /// </summary> /// <param name="connString">连接字符串</param> /// <param name="sql">待执行的sql语句</param> /// <param name="errMsg">如果成功执行,返回受影响的行数, 如果执行失败, 返回错误信息</param> /// <param name="paras">传递给查询的参数</param> /// <returns>成功执行返回true, 否则返回false</returns> public static bool ExecSql(string connString, string sql, out string errMsg, params object[] paras) { return DB.ExecSql(connString, DB.GetParametricSql(sql, paras), out errMsg); }
private static string GetParametricSql(string sql, object[] paras) { sql = sql.Replace("'", "''"); sql = "EXEC sp_executesql N'" + sql + "'" + DB.ParseSqlArgument(paras); return sql; }
/// <summary> /// 参数化ExecSql 函数调用的子函数, 它把对象参数转换成字符串 /// </summary> /// <param name="paras"></param> /// <returns></returns> private static string ParseSqlArgument(object[] paras) { if (paras == null || paras.Length < 1) { return ""; } string text = ",N'"; string text2 = ","; int num = 0; for (int i = 0; i < paras.Length; i++) { object obj; if (paras[i] is Tuple<int, object>) { num = (paras[i] as Tuple<int, object>).Item1; obj = (paras[i] as Tuple<int, object>).Item2; } else { obj = paras[i]; } string text3 = "@p" + num.ToString(); num++; if (obj == null) { throw new Exception("Null argument is not allowed."); } if (obj is string) { string text4 = obj as string; text4 = text4.Replace('\'', '''); object obj2 = text; text = string.Concat(new object[] { obj2, text3, " nvarchar(", (text4.Length > 0) ? text4.Length : 1, ")," }); string text5 = text2; text2 = string.Concat(new string[] { text5, text3, "='", text4, "'," }); } else if (obj is int) { int num2 = (int)obj; text = text + text3 + " int,"; string text6 = text2; text2 = string.Concat(new string[] { text6, text3, "=", num2.ToString(), "," }); } else if (obj is float) { float num3 = (float)obj; text = text + text3 + " float,"; string text7 = text2; text2 = string.Concat(new string[] { text7, text3, "=", num3.ToString(), "," }); } else if (obj is double) { double num4 = (double)obj; text = text + text3 + " real,"; string text8 = text2; text2 = string.Concat(new string[] { text8, text3, "=", num4.ToString(), "," }); } else if (obj is decimal) { decimal num5 = (decimal)obj; string text9 = text; text = string.Concat(new string[] { text9, text3, " decimal(18,", StringTool.GetSectionValue(num5.ToString(), ".", 1).Length.ToString(), ")," }); string text10 = text2; text2 = string.Concat(new string[] { text10, text3, "=", num5.ToString(), "," }); } else if (obj is DateTime) { DateTime dateTime = (DateTime)obj; text = text + text3 + " datetime,"; string text11 = text2; text2 = string.Concat(new string[] { text11, text3, "='", dateTime.ToString("yyyy-MM-dd HH:mm:ss.fff"), "'," }); } else { if (!(obj is char)) { throw new Exception("The data type is not supported currently,please notify the author of this function this error."); } char c = (char)obj; text = text + text3 + " char(1),"; string text12 = text2; text2 = string.Concat(new string[] { text12, text3, "='", c.ToString(), "'," }); } } text = text.Substring(0, text.Length - 1) + "'"; text2 = text2.Substring(0, text2.Length - 1); return text + text2; }
/// <summary> /// 取得一个由分隔符分隔的字符串的分段信息. 如果参数不合法, 将取最接近的值返回,而不返回错误信息 /// </summary> /// <param name="input"></param> /// <param name="sep"></param> /// <param name="section">从0开始编号的段号</param> /// <returns></returns> public static string GetSectionValue(string input, string sep, int section) { string[] array = StringTool.Split(input, sep); if (section >= array.Length) { return ""; } return array[section]; }
/// <summary> /// 根据分割符将字符串割成数组,如果参数非法,返回一个长度为1,首元素为母串的数组, /// 如果母串为null, 则首元素为空串, 不会返回null值. /// </summary> /// <param name="input">母串</param> /// <param name="sep">分割符</param> /// <returns>返回分割后的数组</returns> public static string[] Split(string input, string sep) { if (input == null || sep == null || input == "" || sep == "" || input.IndexOf(sep) < 0) { return new string[] { input ?? "" }; } return input.Split(new string[] { sep }, StringSplitOptions.None); }