/// <summary>
/// 传入一个SQL代码 返回DataSet
/// </summary>
/// <param name="sql">参数SQL 输入SQL语句</param>
/// <returns>返回一个填充过的DataSet</returns>
public DataSet SqlataSet(string sql)
{
if (MySql.State == ConnectionState.Open)//ConnectionState 得到當前資料源聯接的狀態
{
MySql.Close();
}
SqlDataAdapter MyDa = new SqlDataAdapter(sql, MySql);//新建DataSet對象
MyDa.SelectCommand.CommandType = CommandType.Text;
DataSet MyDs = new DataSet();
try
{
MyDa.Fill(MyDs);
}
catch (Exception ex) //資料填充時的異常處理
{
if (MySql.State == ConnectionState.Open) //ConnectionState 得到當前資料源聯接的狀態
{
MySql.Close();
}
MyDs = null;
Err(ex.Message);
}
if (MySql.State == ConnectionState.Open)//ConnectionState 得到當前資料源聯接的狀態
{
MySql.Close();
}
return(MyDs);
}
/// <summary>
/// 调用存贮过程 返回DataSet
/// </summary>
/// <param name="storName">存贮过程 名</param>
public DataSet SqlStor(string storName)
{
SqlDataAdapter StoreDa = new SqlDataAdapter(storName, SqlConnStr);
StoreDa.SelectCommand.CommandType = CommandType.StoredProcedure;//StoredProcedure 为 存贮过程
DataSet StorDataSet = new DataSet();
try
{
StoreDa.Fill(StorDataSet);
}
catch (SqlException ex)
{
//清空变量
StoreDa = null;
StorDataSet.Clear();
StorDataSet = null;
if (MySql.State == ConnectionState.Open)//ConnectionState 得到當前資料源聯接的狀態
{
MySql.Close();
}
Err(ex.Message);
}
if (MySql.State == ConnectionState.Open)//ConnectionState 得到當前資料源聯接的狀態
{
MySql.Close();
}
StoreDa = null;
return(StorDataSet);
}
/// <summary>
/// 调用带参数的 存贮过程 返回DataSet
/// </summary>
/// ///设计思路
///*
/// * 参数1, 所有参数的参数名 如 @parentId=0,parame2 = aspx 以,分开
/// * 所有参数为String 数据类型
/// * string StoreName,string pareme
/// * 拆分字符串一定要使用单引号 ' ' "a a".Split(' ');
/// *
/// <param name="storName">调用的 存贮过程 名</param>
/// <param name="tempparam">存贮过程 的 参数 多个参数以 " , " 格开 参数的值 用" = " 如:@p=12 参数格式为: @parentId=0,parame2 = aspx </param>
public DataSet SqlParameStor(string storName, string tempparam)
{
DataSet Stored_Ds = new DataSet();
string[] StorPar;//拆分参数的数组 拆分 Tempparam
try
{
SqlDataAdapter SqlSA_Stored = new SqlDataAdapter();
SqlSA_Stored.SelectCommand = new SqlCommand(); //建立sqlCommand 类的新实例
SqlSA_Stored.SelectCommand.Connection = new SqlConnection(SqlConnStr); //初始化connection 属性
SqlSA_Stored.SelectCommand.CommandText = storName; //StorName 为存贮过程 或 存贮过程('参数1',参数2)
SqlSA_Stored.SelectCommand.CommandType = CommandType.StoredProcedure; //StoredProcedure 为指定数据操作方法为"调用存贮过程"
if (tempparam.IndexOf(",") > -1 && tempparam != "") //看看此字符串能不能拆分
{
StorPar = tempparam.Split(','); //以' , ' 拆分字符串返回数组
/*
*动态创建 存贮过程 参数 和 对应的值
* StorPar[i].Split('=')[0].ToString();为拆分后的 参数;
* StorPar[i].Split('=')[1].ToString();为拆分后的 参数值;
*/
for (int i = 0; i < StorPar.Length; i++)
{
SqlSA_Stored.SelectCommand.Parameters.Add(StorPar[i].Split('=')[0].ToString(), SqlDbType.Char); //添加一上参数
SqlSA_Stored.SelectCommand.Parameters[StorPar[i].Split('=')[0].ToString()].Value = StorPar[i].Split('=')[1].ToString(); //设定这个参数对应的值
}
}
else//单个参数时执行
{
string[] StorParValue;
StorParValue = tempparam.Split('='); //单个时只以等号拆分
SqlSA_Stored.SelectCommand.Parameters.Add(StorParValue[0].ToString(), SqlDbType.Char); //添加一上参数
SqlSA_Stored.SelectCommand.Parameters[StorParValue[0].ToString()].Value = StorParValue[1].ToString(); //设定这个参数对应的值
}
SqlSA_Stored.Fill(Stored_Ds);// 存贮过程 数据,映照到 临时表 TempTable 中
SqlSA_Stored = null;
}
catch (Exception ex)
{
Stored_Ds.Clear();
Err(ex.Message);
}
if (MySql.State == ConnectionState.Open)//ConnectionState 得到當前資料源聯接的狀態
{
MySql.Close();
}
return(Stored_Ds);
}
public static DataTable GetRemoteDataTable(string command, MySql.Data.MySqlClient.MySqlConnection msqConn)
{
DataTable rdt = new DataTable();
try
{
msqConn.Open();
MySql.Data.MySqlClient.MySqlCommand uploaderCmd = new MySql.Data.MySqlClient.MySqlCommand(command, msqConn);
MySql.Data.MySqlClient.MySqlDataReader onj = uploaderCmd.ExecuteReader();
bool colWasPerf = false;
int colIdx = 0;
List<object> rowValues = new List<object>();
foreach (System.Data.Common.DbDataRecord ro in onj)
{
try
{
// get fields
if (rdt.Columns.Count == 0 && !colWasPerf)
{
for (colIdx = 0; colIdx < ro.FieldCount; colIdx++)
rdt.Columns.Add(ro.GetName(colIdx));
colWasPerf = true;
}
}
catch { }
try
{
for (colIdx = 0; colIdx < ro.FieldCount; colIdx++)
rowValues.Add(ro[colIdx]);
}
catch { }
try
{
rdt.Rows.Add(rowValues.ToArray());
rowValues.Clear();
}
catch { }
}
onj.Close();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
if (msqConn.State == System.Data.ConnectionState.Open)
msqConn.Close();
return rdt;
}