using System;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
namespace XNet
{
/**//**//**//// <summary>
/// 自定义信息对话框
/// </summary>
public class MessageBox
{
/**//**//**//// <summary>
/// 定义一个web页面，用来显示用户自定错误提示信息
/// </summary>
System.Web.UI.Page p;
/**//**//**//// <summary>
/// 实例时，参数为:this 如：MessageBox MB=new MessageBox(this);
/// </summary>
/// <param name="Page">此参数为：this</param>
public MessageBox(System.Web.UI.Page Page)
{
p=Page; //创建页面
}
/**//**//**//// <summary>
/// 显示对话框
/// </summary>
/// <param name="message">提示信息</param>
public void Show(string message)
{
string script="<script> alert('"+ message +"')</script>";
p.Response.Write(script);
}
}
/**//**//**//// <summary>
/// 该类简化了.net 中对Ms Sql Server 中存储过程的调用
/// </summary>
public class SqlOperation
{
private SqlCommand mySqlCommand;//执行数据语句
private SqlConnection Conn;//数据库连结
// private string connString;//数据连结字符串
private ArrayList ParaValues= new ArrayList();//调用存储时， 按参数顺序赋值即：顺序方式。例如：Account('01',1000);
private SortedList NameValues= new SortedList();//调用存储时，使用参数的名称赋值即：参数列表方式。例如：Account(@account_id='01',@account_despiste=1000)
private SortedList outValue = new SortedList();//暂时保存存储过程输出参数的返回值列表
private Boolean bol_ParaSerial;//叛断调用存储时，使用参数列表方式(useNoName=true)还是顺序方式(useNoName=false)。
private Boolean bol_haveOutValue;//判断是否有返回值
private DataSet DbSet = new DataSet();//存储过程返回的数据集
private string myProName;//存储过程的名称
/**//**//**//// <summary>
/// 实例化类时提供数据连结字符串
/// </summary>
/// <param name="connString">数据连结字符串</param>
public SqlOperation(string connString)
{
Conn= new SqlConnection(connString);
}
/**//**//**//// <summary>
/// 实例化类时不提供数据连结字符串
/// </summary>
public SqlOperation()
{
Conn= new SqlConnection(Conn=System.Configuration.ConfigurationSettings.AppSettings["ConStr"]) ;//必要时对连接字符串进行加密;
}
/**//**//**//// <summary>
/// 获取一个Boolean值,叛断所执行的存储过程是否有返回值
/// </summary>
public Boolean bol_haveOutValue
{
get
{
return bol_haveOutValue;
}
}
/**//**//**//// <summary>
/// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值，必须按参数的顺序添加.
/// 用多字符串形式添加所执行的存储过程的参数的值
/// </summary>
/// <param name="values">参数的值.添加方法:AddValueByStrings("a","b","c")</param>
public void AddValueByStrings(params object[] values)
{
// ParaValues.AddRange(values);
for(int i=0;i<= values.Length-1;i++)
{
ParaValues.Add(values[i].ToString());
}
this.bol_ParaSerial=true;
}
/**//**//**//// <summary>
/// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值，必须按参数的顺序添加
/// 不可与 "AddValueByName" 共用,可与 "InsertValueByIndex"、"AddValueByString" 共用
/// </summary>
/// <param name="values">存储过程的值</param>
public void AddValueByIndex(object values)
{
ParaValues.Add(values);
this.bol_ParaSerial=true;
}
/**//**//**//// <summary>
/// 在知道存储过程名称的情况下,可以通过此方法来添加所运行的存储过程的参数值.
/// </summary>
/// <param name="name">存储过程的名称,格式:"@name"必须以"@"开头</param>
/// <param name="values">存储过程的值</param>
public void AddValueByName(string name,object values)
{
this.NameValues.Add(name,values);
this.bol_ParaSerial= false;
}
/**//**//**//// <summary>
/// 在不知道存储过程参数的名称,但是知道参数的顺序可以使用此方法添加参数的值，必须按参数的顺序添加
/// 添加存储过程参数值到指的索引处,不可与 "AddValueByName" 共用,可与 "AddValueByIndex"、"AddValueByString" 共用
/// </summary>
/// <param name="index">在添加参数值的索引位置</param>
/// <param name="values">要添加的参数值</param>
public void InsertValueByIndex(int index,object values)
{
this.ParaValues.Insert(index,values);
this.bol_ParaSerial = false;
}
/**//**//**//// <summary>
/// 清除传递给存储过程参数的值
/// </summary>
public void ClearValue()
{
this.ParaValues.Clear();
this.NameValues.Clear();
this.DbSet.Clear();
}
/**//**//**//// <summary>
/// 获取已经赋值的参数的个数
/// </summary>
public int ValueCount
{
get
{
return this.bol_ParaSerial== true? this.ParaValues.Count:this.NameValues.Count;
}
}
/**//**//**//// <summary>
/// 获取或设置当前要运行的存储过程的名称
/// </summary>
public string ProcedureName
{
get
{
return myProName;
}
set
{
myProName=value;
}
}
/**//**//**//// <summary>
/// 通过索引得到返回参数值的名称
/// </summary>
/// <param name="index">参数索引</param>
/// <returns>返回 string 类型</returns>
public string GetOutValuesNameByIndex(int index)
{
return Convert.ToString(this.outValue.GetKey(index));
}
/**//**//**//// <summary>
/// 通过索引得到返回参数的值
/// </summary>
/// <param name="index">参数索引</param>
/// <returns>返回 object 类型</returns>
public object GetOutValuesValueByIndex(int index)
{
return this.outValue.GetByIndex(index);
}
/**//**//**//// <summary>
/// 通过参数的名称得到返回参数的值
/// </summary>
/// <param name="name">参数名称</param>
/// <returns>返回 object 类型</returns>
public object GetOutValuesValueByName(string name)
{
// return this.outValue.GetByIndex(this.outValue.IndexOfKey(name));
return this.outValue[name];
}
/**//**//**//// <summary>
/// 获取储存过程返回值的数量
/// </summary>
public int OutValueCount
{
get
{
return this.outValue.Count;
}
}
/**//**//**//// <summary>
/// 运行存储过程
/// </summary>
/// <param name="proName"></param>
private void goProc(string proName )
{
string selectProcInfo;
selectProcInfo="select specific_name as ProcName,parameter_Name as ParaName,Data_Type as TypeName,ORDINAL_POSITION as Colid,parameter_Mode as Isoutparam from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + proName + "' order by ORDINAL_POSITION";
this.mySqlCommand= new SqlCommand(selectProcInfo,this.Conn);
SqlDataAdapter myDP = new SqlDataAdapter(this.mySqlCommand);
myDP.Fill(this.DbSet,"ProcInfo");//等到当前存储过程的信息:参数,参数的顺序, 参数的类型,参数的传递
this.mySqlCommand.CommandText= proName;
this.mySqlCommand.CommandType= CommandType.StoredProcedure;
if (this.DbSet.Tables["ProcInfo"].Rows.Count>0)//(this.NameValues.Count>0||this.ParaValues.Count>0)&&
{
for(int i=0;i<=this.DbSet.Tables["ProcInfo"].Rows.Count-1;i++)
{
SqlDbType sqlDbType;
sqlDbType=SqlDbType.Int;
switch (Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["TypeName"]))
{
case "image":
{
sqlDbType = SqlDbType.Image;
}
break;
case "text":
{
sqlDbType = SqlDbType.Text;
}
break;
case "uniqueidentifier":
{
sqlDbType = SqlDbType.UniqueIdentifier;
}
break;
case "tinyint":
{
sqlDbType = SqlDbType.TinyInt;
}
break;
case "smallint":
{
sqlDbType = SqlDbType.SmallInt;
}
break;
case "int":
{
sqlDbType = SqlDbType.Int;
}
break;
case "smalldatetime":
{
sqlDbType = SqlDbType.SmallDateTime;
}
break;
case "real":
{
sqlDbType= SqlDbType.Real;
}
break;
case "money":
{
sqlDbType = SqlDbType.Money;
}
break;
case "datetime":
{
sqlDbType = SqlDbType.DateTime;
}
break;
case "float":
{
sqlDbType = SqlDbType.Float;
}
break;
case "ntext":
{
sqlDbType = SqlDbType.NText;
}
break;
case "bit":
{
sqlDbType = SqlDbType.Bit;
}
break;
case "decimal":
{
sqlDbType = SqlDbType.Decimal;
}
break;
case "smallmoney":
{
sqlDbType = SqlDbType.SmallMoney;
}
break;
case "BigInt":
{
sqlDbType = SqlDbType.BigInt;
}
break;
case "varbinary":
{
sqlDbType = SqlDbType.VarBinary;
}
break;
case "varchar":
{
sqlDbType = SqlDbType.VarChar;
}
break;
case "binary":
{
sqlDbType = SqlDbType.Binary;
}
break;
case "char":
{
sqlDbType = SqlDbType.Char;
}
break;
case "timestamp":
{
sqlDbType = SqlDbType.Timestamp;
}
break;
case "nvarchar":
{
sqlDbType = SqlDbType.NVarChar;
}
break;
case "nchar":
{
sqlDbType = SqlDbType.NChar;
}
break;
default:
break;
}
string parameterName= Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["ParaName"]);
SqlParameter P = new SqlParameter(parameterName,sqlDbType);
if(Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["Isoutparam"])=="INOUT")
{
P.Direction = ParameterDirection.Output;
this.bol_haveOutValue = true;
}
else
{
if (this.bol_ParaSerial == true)
{
P.Value = (sqlDbType==SqlDbType.Bit?Convert.ToByte(this.ParaValues[i]):this.ParaValues[i]);
}
else
{
P.Value = (sqlDbType==SqlDbType.Bit?Convert.ToByte(this.NameValues[parameterName]):this.NameValues[parameterName]);
}
}
this.mySqlCommand.Parameters.Add(P);
}
}
this.myProName= proName;
}
private void OutoutValue()
{
if (this.bol_haveOutValue== true)
{
for(int i=0;i<=this.DbSet.Tables["ProcInfo"].Rows.Count-1;i++)
{
if (Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["Isoutparam"])=="INOUT")
{
this.outValue.Add(Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["ParaName"]),this.mySqlCommand.Parameters[Convert.ToString(this.DbSet.Tables["ProcInfo"].Rows[i]["ParaName"])].Value);
}
}
}
this.ClearValue();
}
/**//**//**//// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataSet。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>返回类型 DataSet </returns>
public DataSet ProcReturnDataSet(string procedureName)
{
this.goProc(procedureName);
DataSet DBS= new DataSet();
SqlDataAdapter ap = new SqlDataAdapter(this.mySqlCommand);
ap.Fill(DBS);
this.OutoutValue();
return DBS;
}
/**//**//**//// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataTable。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <param name="tableName">指定所返回的DataTable的TableName</param>
/// <returns>返回类型 DataTable </returns>
public DataTable ProcReturnDataTable(string procedureName,string tableName)
{
this.goProc(procedureName);
DataTable dataTable= new DataTable();
dataTable.TableName=tableName;
SqlDataAdapter ap = new SqlDataAdapter(this.mySqlCommand);
ap.Fill(dataTable);
this.OutoutValue();
return dataTable;
}
/**//**//**//// <summary>
/// 通过存储过程的名称执行存储过程,返回 DataTable。
/// </summary>
/// <param name="procedureName">存储过程的名称</param>
/// <returns>返回类型 DataTable </returns>
public DataTable ProcReturnDataTable(string procedureName)
{
this.goProc(procedureName);
DataTable dataTable= new DataTable();
SqlDataAdapter ap = new SqlDataAdapter(this.mySqlCommand);
ap.Fill(dataTable);
this.OutoutValue();
return dataTable;
}
/**//**//**//// <summary>
/// 执行指定的存储过程。无返回类型。
/// </summary>
/// <param name="procedureName">存储过程名称</param>
public void RunProcedure(string procedureName)
{
this.goProc(procedureName);
this.Conn.Open();
this.mySqlCommand.ExecuteNonQuery();
this.OutoutValue();
this.Conn.Close();
}
/**//**//**//// <summary>
/// 执行SQL语句。返回 DataSet
/// </summary>
/// <param name="sql">所要执行的SQL语句</param>
/// <returns>DataSet</returns>
public DataSet RunSqlReturnDataSet(string sql,string tableName)
{
SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
DataSet retrunDataSet = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(retrunDataSet,tableName);
return retrunDataSet;
}
/**//**//**//// <summary>
/// 执行SQL语句。返回 DataTable
/// </summary>
/// <param name="sql">所要执行的SQL语句</param>
/// <param name="tableName">返回tableName的名称</param>
/// <returns>DataTable</returns>
public DataTable RunSqlReturnDataTable(string sql,string tableName)
{
SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
DataTable retrunDataTable = new DataTable();
retrunDataTable.TableName=tableName;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(retrunDataTable);
return retrunDataTable;
}
/**//**//**//// <summary>
/// 执行SQL语句。返回Boolean类型
/// </summary>
/// <param name="sql">所要执行的SQL语句</param>
/// <returns>Boolean</returns>
public Boolean RunSql(string sql)
{
try
{
SqlCommand sqlCommand= new SqlCommand(sql,this.Conn);
this.Conn.Open();
sqlCommand.ExecuteNonQuery();
this.Conn.Close();
return true;
}
catch
{
return false;
}
}
}
}
