2023年6月21日发(作者：)
mysql存储过程类型转换_C#MYSQL存储过程参数动态获取及类型转换/// 获取存储过程的参数,⽣成存储过程调⽤参数列表////// 存储过程名称/// 存储过程调⽤参数值集合///private MySqlParameter[] GetMySQLParameters(string ProcedureName, object[] parameters){DataTable dt = new DataTable();string sqlString = "select * from INFORMATION_TERS where SPECIFIC_NAME='" + ProcedureName + "'order by ORDINAL_POSITION";if (!Select(sqlString, dt))return null;List sqlParameters = new List();for (int i = 0; i < ; i++){MySqlParameter sqlParameter = new MySqlParameter();terName = [i]["PARAMETER_NAME"].ToString();ion = ([i]["PARAMETER_MODE"].ToString() == "IN") ? :;#region 匹配参数类型switch ([i]["DATA_TYPE"].ToString().ToLower ()){case "bit":if (ion == ) = (bool)(parameters[i]);bType = ;break;case "int64":if (ion == ) = 64(parameters[i]);bType = 64;break;case "int":case "int32":if (ion == )if (parameters[i] == )//参数为DBNull时{ = ;}else{ = 32(parameters[i]);}bType = 32;break;case "decimal":if (ion == ) = mal(parameters[i]);bType = l;ion = ([i]["NUMERIC_PRECISION"]); = ([i]["NUMERIC_SCALE"]);break;case "varchar":if (ion == ) = parameters[i]; = (int)[i]["CHARACTER_MAXIMUM_LENGTH"];bType = r;break;case "tinyblob":if (ion == ) = (string)parameters[i];bType = ob;break;case "meduimblob":if (ion == ) = (string)parameters[i];bType = Blob;break;case "longblob":if (ion == ) = (string)parameters[i];bType = ob;break;case "blob":if (ion == ) = (string)parameters[i];bType = ;break;case "tinytext":if (ion == ) = (string)parameters[i];bType = xt;break;case "text":if (ion == ) = (string)parameters[i];bType = ;break;case "datetime":if (ion == ) = parameters[i];bType = me;break;case "smalldatetime":if (ion == ) = (DateTime)parameters[i];bType = me;break;case "uniqueidentifier":bType = ;break;default: break;}#(sqlParameter);}return y ();}////// 调⽤存储过程基本⽅法，返回DataSet结果////// 存储过程/// IDataParameter参数///public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters){using (MySqlConnection connection = new MySqlConnection(tString)){();MySqlCommand cmd = new MySqlCommand(storedProcName, connection);dType = Procedure;if (parameters != null){foreach (MySqlParameter parameter in parameters){if ((ion == utput || ion == ) &&( == null)){ = ;}(parameter);}}using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)){DataSet ds = new DataSet();try{(ds, "ds");();}catch (xception ex){throw new Exception(e);}return ds;}}}//调⽤存储过程返回DataSet结果集：public Dataset GetResultByProcedure(string ProcedureName, object[] parameters){MySqlParameter[] sqlParameters = GetMySQLParameters(ProcedureName,parameters);DataSet ds= RunProcedure(ProcedureName, sqlParameters);return ds;}实际使⽤：如通过ID获取某城市信息，存储过程为Procedure_GetCityByID，参数为ID，ID=1，则：Dataset ds=GetResultByProcedure("Procedure_GetCityByID",1)
2023年6月21日发(作者：)
mysql存储过程类型转换_C#MYSQL存储过程参数动态获取及类型转换/// 获取存储过程的参数,⽣成存储过程调⽤参数列表////// 存储过程名称/// 存储过程调⽤参数值集合///private MySqlParameter[] GetMySQLParameters(string ProcedureName, object[] parameters){DataTable dt = new DataTable();string sqlString = "select * from INFORMATION_TERS where SPECIFIC_NAME='" + ProcedureName + "'order by ORDINAL_POSITION";if (!Select(sqlString, dt))return null;List sqlParameters = new List();for (int i = 0; i < ; i++){MySqlParameter sqlParameter = new MySqlParameter();terName = [i]["PARAMETER_NAME"].ToString();ion = ([i]["PARAMETER_MODE"].ToString() == "IN") ? :;#region 匹配参数类型switch ([i]["DATA_TYPE"].ToString().ToLower ()){case "bit":if (ion == ) = (bool)(parameters[i]);bType = ;break;case "int64":if (ion == ) = 64(parameters[i]);bType = 64;break;case "int":case "int32":if (ion == )if (parameters[i] == )//参数为DBNull时{ = ;}else{ = 32(parameters[i]);}bType = 32;break;case "decimal":if (ion == ) = mal(parameters[i]);bType = l;ion = ([i]["NUMERIC_PRECISION"]); = ([i]["NUMERIC_SCALE"]);break;case "varchar":if (ion == ) = parameters[i]; = (int)[i]["CHARACTER_MAXIMUM_LENGTH"];bType = r;break;case "tinyblob":if (ion == ) = (string)parameters[i];bType = ob;break;case "meduimblob":if (ion == ) = (string)parameters[i];bType = Blob;break;case "longblob":if (ion == ) = (string)parameters[i];bType = ob;break;case "blob":if (ion == ) = (string)parameters[i];bType = ;break;case "tinytext":if (ion == ) = (string)parameters[i];bType = xt;break;case "text":if (ion == ) = (string)parameters[i];bType = ;break;case "datetime":if (ion == ) = parameters[i];bType = me;break;case "smalldatetime":if (ion == ) = (DateTime)parameters[i];bType = me;break;case "uniqueidentifier":bType = ;break;default: break;}#(sqlParameter);}return y ();}////// 调⽤存储过程基本⽅法，返回DataSet结果////// 存储过程/// IDataParameter参数///public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters){using (MySqlConnection connection = new MySqlConnection(tString)){();MySqlCommand cmd = new MySqlCommand(storedProcName, connection);dType = Procedure;if (parameters != null){foreach (MySqlParameter parameter in parameters){if ((ion == utput || ion == ) &&( == null)){ = ;}(parameter);}}using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)){DataSet ds = new DataSet();try{(ds, "ds");();}catch (xception ex){throw new Exception(e);}return ds;}}}//调⽤存储过程返回DataSet结果集：public Dataset GetResultByProcedure(string ProcedureName, object[] parameters){MySqlParameter[] sqlParameters = GetMySQLParameters(ProcedureName,parameters);DataSet ds= RunProcedure(ProcedureName, sqlParameters);return ds;}实际使⽤：如通过ID获取某城市信息，存储过程为Procedure_GetCityByID，参数为ID，ID=1，则：Dataset ds=GetResultByProcedure("Procedure_GetCityByID",1)
发布评论