using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
namespace SQLServerBase
{
/// <summary>
///内部类:存储过程的返回值记录类
/// </summary>
public class SqlResult
{
public bool Succeed; //存储过程是否执行成功.
public Hashtable OutputValues; // 存储过程output值,放在(HashTable)表OutputValues里.
public DataTable datatable; //存储过程返回的结果集,放在(DataTable)表datatable里.
public DataSet dataSet; //存储过程返回的结果集,放在DataSet表中
public string errorMessage; //访问数据库失败
public int inflecntNum;
public SqlResult()
{
Succeed = false;
OutputValues = new Hashtable();
datatable=new DataTable();
dataSet=new DataSet();
errorMessage = "";
}
}
/// <summary>
/// ====================***调用存储过程和SQL的基类***============================
/// abstract:该类不能被实例化,只能通过派生子类来使用它
/// </summary>
public abstract class SpSQL_base : IDisposable
{
public SpSQL_base() : this("","")
{
}
//重载
public SpSQL_base(string sp_name,string sql_name)
{
this.PRocedureName = sp_name;
this.SQLName = sql_name;
}
//私有成员变量
private string sp_name;
private string sql_name;
private SqlConnection myConnection;
private SqlCommand myCommand;
private SqlParameter myParameter;//存储过程参数
//公共属性
public string ProcedureName//获取和设置存储过程名
{
get
{
return this.sp_name;
}
set
{
this.sp_name = value;
}
}
//公共属性
public string SQLName//获取和设置存储过程名
{
get
{
return this.sql_name;
}
set
{
this.sql_name = value;
}
}
/// <summary>
/// 调用存储过程
/// </summary>
/// <param name="parameters">参数集合</param>
/// <returns></returns>
public SqlResult Call_SP(params object[] parameters)
{
string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
//存储过程的返回值记录类
SqlResult result = new SqlResult();
myConnection = new SqlConnection(strconn);
myCommand = new SqlCommand(this.ProcedureName, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myConnection.Open();
//将参数添加到存储过程的参数集合
GetProcedureParameter(result,parameters);
//开始事物
using(SqlTransaction trans = myConnection.BeginTransaction())
{
try
{
if(trans!=null)
{
myCommand.Transaction = trans;
}
//填充数据,将结果填充到SqlResult集中
myAdapter.Fill(result.dataSet);
if(result.dataSet.Tables.Count>0)
result.datatable=result.dataSet.Tables[0].Copy();
//将输出参数的值添加到Result的OutputValues
GetOutputValue(result);
//提交事物
trans.Commit();
}
catch(Exception e)
{
result.errorMessage = e.Message;
//事物回滚
trans.Rollback();
}
//如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
finally
{
myAdapter.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
}
}
return result;
}
/// <summary>
/// 将参数添加到存储过程的参数集合
/// </summary>
/// <param name="parameters"></param>
private void GetProcedureParameter(SqlResult result,params object[] parameters)
{
SqlCommand myCommand2 = new SqlCommand();
myCommand2.Connection = this.myConnection;
myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";
SqlDataReader reader = null;
try
{
reader = myCommand2.ExecuteReader();
int i = 0;
while(reader.Read())
{
myParameter = new SqlParameter();
myParameter.ParameterName = reader["PARAMETER_NAME"].ToString();
myParameter.Direction = reader["PARAMETER_MODE"].ToString()=="IN"?ParameterDirection.Input:ParameterDirection.Output;
switch(reader["DATA_TYPE"].ToString())
{
//bigint
case "bigint":
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToInt64(parameters[i]);
myParameter.SqlDbType = SqlDbType.BigInt;
break;
//binary
//bit
case "bit" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToBoolean(parameters[i]);
myParameter.SqlDbType = SqlDbType.Bit;
break;
//char
case "char" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (string)parameters[i];
myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
myParameter.SqlDbType = SqlDbType.Char;
break;
//datetime
case "datetime" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToDateTime(parameters[i]);
myParameter.SqlDbType = SqlDbType.DateTime;
break;
//decimal
case "decimal" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (decimal)parameters[i];
myParameter.SqlDbType = SqlDbType.Decimal;
myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];
myParameter.Scale = byte.Parse(reader["NUMERIC_SCALE"].ToString());
break;
//float
case "float" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (float)parameters[i];
myParameter.SqlDbType = SqlDbType.Float;
break;
//image
case "image" :
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(byte[])parameters[i];
}
myParameter.SqlDbType = SqlDbType.Image;
break;
//int
case "int" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToInt32(parameters[i].ToString());
myParameter.SqlDbType = SqlDbType.Int;
break;
//money
case "money":
if(myParameter.Direction==ParameterDirection.Input)
myParameter.Value=Convert.ToDecimal(parameters[i]);
myParameter.SqlDbType=SqlDbType.Money;
break;
//nchar
case "nchar" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (string)parameters[i];
myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
myParameter.SqlDbType = SqlDbType.NChar;
break;
//ntext
case "ntext" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (string)parameters[i];
myParameter.SqlDbType = SqlDbType.NText;
break;
//numeric
case "numeric" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (decimal)parameters[i];
myParameter.SqlDbType = SqlDbType.Decimal;
myParameter.Precision = (byte)reader["NUMERIC_PRECISION"];
myParameter.Scale = byte.Parse(reader["NUMERIC_SCALE"].ToString());
break;
//nvarchar
case "nvarchar" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToString(parameters[i]);
myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
myParameter.SqlDbType = SqlDbType.NVarChar;
break;
//real
case "real":
if(myParameter.Direction==ParameterDirection.Input)
myParameter.Value=Convert.ToSingle(parameters[i]);
myParameter.SqlDbType = SqlDbType.Real;
break;
//smalldatetime
case "smalldatetime" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToDateTime(parameters[i]);
myParameter.SqlDbType = SqlDbType.DateTime;
break;
//smallint
case "smallint" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToInt16(parameters[i].ToString());
myParameter.SqlDbType = SqlDbType.SmallInt;
break;
//smallmoney
case "smallmoney":
if(myParameter.Direction==ParameterDirection.Input)
myParameter.Value=Convert.ToDecimal(parameters[i]);
myParameter.SqlDbType=SqlDbType.SmallMoney;
break;
//sql_variant
//text
case "text" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (string)parameters[i];
myParameter.SqlDbType = SqlDbType.Text;
break;
//timestamp
//tinyint
case "tinyint":
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = Convert.ToByte(parameters[i]);
myParameter.SqlDbType = SqlDbType.TinyInt;
break;
//uniqueidentifier
//varbinary
case "varbinary":
if(myParameter.Direction==ParameterDirection.Input)
myParameter.Value=(byte[])parameters[i];
myParameter.SqlDbType = SqlDbType.VarBinary;
break;
//varchar
case "varchar" :
if(myParameter.Direction == ParameterDirection.Input)
myParameter.Value = (string)parameters[i];
myParameter.Size = Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]);
myParameter.SqlDbType = SqlDbType.VarChar;
break;
default :
break;
}
i++;
myCommand.Parameters.Add(myParameter);
}
}
catch(Exception e)
{
result.errorMessage = e.Message;
}
finally
{
if(reader!=null)
{
reader.Close();
}
myCommand2.Dispose();
}
}
/// <summary>
/// 将输出的值添加到Result的OutputValues
/// </summary>
/// <param name="result"></param>
private void GetOutputValue(SqlResult result)
{
if(result.Succeed==false)
{
result.Succeed=true;
}
foreach(SqlParameter parameter in myCommand.Parameters)
{
if(parameter.Direction == ParameterDirection.Output)
{
//Hashtab表是一个键值对
result.OutputValues.Add(parameter.ParameterName, parameter.Value);
}
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true);
}
protected virtual void Dispose(bool disposing)
{
if (! disposing)
return;
if(myConnection != null)
{
myConnection.Dispose();
}
}
//=======end======
//=======begin====
/// <summary>
/// 调用SQL的基类
/// </summary>
/// <param name="parameters">参数集合</param>
/// <returns></returns>
public SqlResult Call_SQL()
{
string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
//存储过程的返回值记录类
SqlResult result = new SqlResult();
myConnection = new SqlConnection(strconn);
myCommand = new SqlCommand(this.sql_name, myConnection);
myCommand.CommandType = CommandType.Text;
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myConnection.Open();
using(SqlTransaction trans = myConnection.BeginTransaction())
{
try
{
if(trans!=null)
{
myCommand.Transaction = trans;
}
//填充数据,将结果填充到SqlResult集中
myAdapter.Fill(result.datatable);
result.Succeed = true;
//提交事物
trans.Commit();
}
catch(Exception e)
{
result.Succeed = false;
result.errorMessage = e.Message;
}
//如果捕捉了异常，但仍会执行包括在 finally 块中的输出语句
finally
{
myAdapter.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
}
}
return result;
}
//=======end=========
}
}
继承此类后直接调用，如下：DB：Northwind
public class DATest : SpSQL_base
{
public DATest()
{}
public SqlResult SQLTest()
{
base.SQLName="select EmployeeID,LastName from dbo.Employees";
return base.Call_SQL();
}
public SqlResult SPTest()
{
base.ProcedureName="CustOrderHist";
return base.Call_SP("ALFKI");
}
}配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString" value="server=BIM-7C67612053C;database=Northwind;uid=sa;pwd=;" />
</appSettings>
</configuration>显示查询结果：
private void DataShow_Load(object sender, System.EventArgs e)
{
DATest da=new DATest();
if(da.SPTest().Succeed && da.SQLTest().Succeed)
{
this.datasp.DataSource=da.SPTest().datatable;
this.dataSQL.DataSource=da.SQLTest().datatable;
}
}
此类还有待完善，诸如存储过程参数为//binary、//sql_variant、//timestamp、//uniqueidentifier这些类型时还不能执行查询，其余BUG请大家多多指正～～
感谢以前一起在二炮工作过的师兄们提供源代码，小弟只是做简单修改，谢谢各位师兄！
出处：shanvenleo BLOG
