www.pudn.com > Crawler_bemjh.rar > SqlBase.cs
using System;
using System.Data;
using System.Data .SqlClient ;
using System.Configuration ;
using System.Diagnostics ;
using System.Collections;
namespace CrawlerLib
{
///
/// general operation to the database
/// 提供了一组操作sql数据库的公共方法
///
public class SqlBase
{
private static string connectionString = "server=localhost;uid=sa;pwd=;database=TopicSpider-mjh";
#region Connect Message
///
/// 连接字符串
///
public static void setConnectionString(ConnectionString cs)
{
connectionString = "server="+cs.DataBaseAddress+";uid="+cs.DataBaseUserName+";pwd="+cs.DataBasePassword+";database="+cs.DataBaseName;
}
///
/// get the connect object
///
///
/// 执行SqlDataReader方法
///
/// 连接对象
/// SQl表达式。存储过程名|Sql语句
/// DataReader对象
public static SqlDataReader ExecuteDataReader(string sql,CommandType type)
{
using(SqlConnection con = SqlBase.GetConnection())
{
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
con.Open();
SqlCommand command = con.CreateCommand();
command.CommandText = sql;
command.CommandType = type;
SqlDataReader read = command.ExecuteReader(CommandBehavior.CloseConnection);
return read;
}
}
///
/// 使用存储过程
///
///
/// 存储过程名|sql语句
/// 存储过程参数集合
/// DataReader对象
public static SqlDataReader ExecuteDataReader(string sql,CommandType type,SqlParameter[] param)
{
using(SqlConnection con = SqlBase.GetConnection())
{
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
con.Open();
SqlCommand command = con.CreateCommand();
command.CommandText = sql;
command.CommandType = type;
foreach(SqlParameter p in param)
{
command.Parameters .Add(p);
}
SqlDataReader read = command.ExecuteReader(CommandBehavior.CloseConnection);
return read;
}
}
///
/// 使用存储过程
///
/// 存储过程名|sql语句
/// DataTabel对象
public static DataTable getDataTable(string sql)
{
using(SqlConnection con = SqlBase.GetConnection())
{
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
con.Open();
SqlDataAdapter da=new SqlDataAdapter(sql,con);
DataSet ds=new DataSet();
da.Fill(ds,"words");
DataTable dt=ds.Tables["words"];
return dt;
}
}
#endregion
#region Return Data Object
///
/// 返回dataset对象
///
/// 存储过程|sql语句
/// DataSet
public static DataSet ExecuteDataSet(string sql,CommandType type)
{
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
using(SqlConnection con = SqlBase.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand .CommandType = type;
da.SelectCommand .CommandText = sql;
da.SelectCommand .Connection = con;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
///
/// 使用DataSet自带的分页方法
///
/// 连接字符串
/// 当前页
/// 每页显示的数据量
/// 表名
/// 总记录条数
/// 返回一定数量的数据集
public static DataView ExecuteDataView(string sql,int currentPage,int pagesize,string tableName)
{
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
using(SqlConnection con = SqlBase.GetConnection())
{
con.Open();
int start = (currentPage-1)*pagesize;
SqlDataAdapter da = new SqlDataAdapter(sql,con);
DataSet ds = new DataSet();
da.Fill(ds,start,pagesize,tableName);
return ds.Tables[0].DefaultView;
}
}
///
/// use SqlDataAdapter to fill the DataTable
///
/// 存储过程|sql语句
/// DataTable
public static DataTable ExecuteDataTable(string sql,CommandType type)
{
string x;
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
using(SqlConnection con = SqlBase.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(sql,con);
da.SelectCommand .CommandType = type;
DataTable dtt = new DataTable();
da.Fill(dtt);
return dtt;
}
}
public static DataTable ExecuteDataTable(string sql,SqlParameter[] param,CommandType type)
{
if(sql == null || sql == "")
throw new Exception("Sql Expression is null,please allocate it");
using(SqlConnection con = SqlBase.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(sql,con);
da.SelectCommand .CommandType = type;
foreach(SqlParameter p in param)
{
da.SelectCommand .Parameters .Add(p);
}
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
#endregion
#region Executscalar Method
///
/// return a single value of the sql expression effected
///
/// CommandText
/// eturn a single value of the sql expression effected
public static void ExecuteScalar(string sqlString,out int returnValue)
{
if(sqlString == null || sqlString == "")
throw new Exception("Sql Expression is null,please allocate it");
using(SqlConnection con = SqlBase.GetConnection())
{
con.Open();
SqlCommand command = new SqlCommand (sqlString,con);
returnValue = (int)command.ExecuteScalar();
}
}
#endregion
#region ExecutQuery方法
///
/// execute the sql expression according to the sql
///
/// 连接对象
/// 存储过程|sql语句
/// 执行方式
public static void ExecuteQuery(string sqlString,CommandType type)
{
using(SqlConnection con = SqlBase.GetConnection())
{
if(sqlString == null || sqlString == "")
throw new Exception("Sql Expression is null,please allocate it");
con.Open();
SqlCommand command = new SqlCommand ();
command.Connection = con;
command.CommandText = sqlString;
command.CommandType = type;
command.ExecuteNonQuery();
}
}
///
/// 带参数的执行过程
///
/// 连接对象
/// 存储过程|sql语句
/// Command参数数组
/// 执行方式
public static void ExecuteQuery(string sqlString,SqlParameter[] param,CommandType type)
{
if(sqlString == null || sqlString == "")
throw new Exception("Sql Expression is null,please allocate it");
using(SqlConnection con = SqlBase.GetConnection())
{
con.Open();
SqlCommand command = con.CreateCommand();
command.CommandText = sqlString;
command.CommandType = type;
foreach(SqlParameter p in param)
{
command.Parameters .Add(p);
}
command.ExecuteNonQuery();
}
}
#endregion
#region Create Param Method
///
/// Create a SqlParameter and initialize it to the passed in values
///
/// The name of the parameter being created.
/// The type in database SqlBase parameter represents.
/// The size of the parameter type if applicable.
/// The direction the parameter is sending data.
/// The value of the parameter. Can Only assign a value if "direction" is Input or InputOutput.
/// An initialized SqlParameter
public static SqlParameter CreateParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object oValue)
{
SqlParameter sp;
if (size != 0)
sp = new SqlParameter(paramName, dbType, size);
else
sp = new SqlParameter(paramName, dbType);
sp.Direction = direction;
//Only assign a value for Input or InputOutput parameters
if (oValue != null && (direction == ParameterDirection.Input || direction == ParameterDirection.InputOutput))
sp.Value = oValue;
return sp;
}
#endregion
#region 数据库维护(备份,还原,压缩)
///
/// 压缩数据库
///
/// 数据库名称
/// 备份路径
public static void BackUpDatabase(string DBName,string path)
{
Debug.Assert (DBName != "" || DBName != null,"Invalid DBName,please check it and try again");
Debug.Assert(path != "" || path != null,"Invalid file Path,please check it and try again");
using(SqlConnection con = SqlBase .GetConnection())
{
con.Open();
SqlBase.ExecuteQuery("BACKUP DATABASE "+DBName+" TO DISK = '"+ path +"'",CommandType.Text);
}
}
///
/// 还原数据库
/// 还原数据库的时候必须借助系统表来实现,即关闭原数据库的进程,在还原,否则会冲突
///
/// 数据库名
/// 还原路径
public static void RestoreDataBase(string DBName,string path)
{
string sql = null;
Debug.Assert (DBName != "" || DBName != null,"Invalid DBName,please check it and try again");
Debug.Assert(path != "" || path != null,"Invalid file Path,please check it and try again");
sql = "SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID('"+ DBName +"')";
SqlDataReader read = SqlBase.ExecuteDataReader(sql,CommandType.Text);
while(read.Read())
{
sql += "kill " + read[0].ToString();
}
read.Close();
SqlBase.ExecuteQuery(sql,CommandType.Text);
sql = "RESTORE DATABASE "+DBName+" from DISK = '"+ path +"'";
SqlBase.ExecuteQuery(sql,CommandType.Text);
}
///
/// 收缩数据库(需指定收缩大小)
///
/// 数据库名
/// 收缩数据库的大小
public static void ShrinkDB(string DBName,int shrinkSize)
{
Debug.Assert (DBName != "" || DBName != null,"Invalid DBName,please check it and try again");
string sql = "DBCC SHRINKDATABASE(" + DBName + "," +shrinkSize+")";
SqlBase.ExecuteQuery(sql,CommandType.Text);
}
///
/// 收缩数据库(不需指定收缩大小,将收缩空间释放给系统)
///
/// 数据库名
public static void ShrinkDB(string DBName)
{
Debug.Assert (DBName != "" || DBName != null,"Invalid DBName,please check it and try again");
string sql = "DBCC SHRINKDATABASE(" + DBName + ",TRUNCATEONLY)";
SqlBase.ExecuteQuery(sql,CommandType.Text);
}
#endregion
}
}