/// <summary>
/// use datareader to get the data from database
/// </summary>
/// <param name="con">database connection object</param>
/// <param name="sql">procedure name or sql expression</param>
/// <returns>DataReader object</returns>
public static SqlDataReader ExecuteDataReader(string sql, CommandType type)
{
if (sql == null || sql == "")
{
throw new Exception(nullSqlExpress);
}
SqlConnection con = SqlBase.GetConnection();
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
SqlCommand command = con.CreateCommand();
command.CommandText = sql;
command.CommandType = type;
SqlDataReader read = command.ExecuteReader(CommandBehavior.CloseConnection);
return(read);
}
/// <summary>
/// execute the procedure with params
/// </summary>
/// <param name="con"></param>
/// <param name="sqlString">procedure name or sql expression</param>
/// <param name="param">params array</param>
/// <param name="type" >execute style</param>
public static int ExecuteQuery(string sqlString, SqlParameter[] param, CommandType type)
{
if (sqlString == null || sqlString == "")
{
throw new Exception(nullSqlExpress);
}
using (SqlConnection con = SqlBase.GetConnection())
{
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
SqlCommand command = con.CreateCommand();
command.CommandText = sqlString;
command.CommandType = type;
foreach (SqlParameter p in param)
{
command.Parameters.Add(p);
}
return((int)command.ExecuteNonQuery());
}
}
public static DataTable ExecuteDataTable(string sql, SqlParameter[] param, CommandType type)
{
if (sql == null || sql == "")
{
throw new Exception(nullSqlExpress);
}
using (SqlConnection con = SqlBase.GetConnection())
{
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = con;
da.SelectCommand.CommandText = sql;
da.SelectCommand.CommandType = type;
foreach (SqlParameter p in param)
{
da.SelectCommand.Parameters.Add(p);
}
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
}
/// <summary>
/// get the data with dataset object
/// </summary>
/// <param name="sql">procedure name or sql expression</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(string sql, CommandType type)
{
if (sql == null || sql == "")
{
throw new Exception(nullSqlExpress);
}
using (SqlConnection con = SqlBase.GetConnection())
{
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.CommandType = type;
da.SelectCommand.CommandText = sql;
da.SelectCommand.Connection = con;
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="DBName">数据库名称</param>
/// <param name="newDBName">备份数据库名称</param>
/// <param name="path">备份路径</param>
public static bool BackUpDatabase(string DBName, string newDBName, 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");
if (!path.EndsWith("\\"))
{
path += "\\";
}
using (SqlConnection con = SqlBase.GetConnection())
{
con.Open();
if (System.IO.File.Exists(path + newDBName + ".dat"))
{
System.IO.File.Delete(path + newDBName + ".dat");
}
SqlBase.ExecuteQuery("BACKUP DATABASE " + DBName + " TO DISK = '" + path + newDBName + ".dat'", CommandType.Text);
return(true);
}
}
/// <summary>
/// return a single value of the sql expression effected
/// </summary>
/// <param name="sqlString">CommandText</param>
/// <returns>eturn a single value of the sql expression effected</returns>
public static void ExecuteScalar(string sqlString, out int returnValue)
{
if (sqlString == null || sqlString == "")
{
throw new Exception(nullSqlExpress);
}
using (SqlConnection con = SqlBase.GetConnection())
{
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
SqlCommand command = new SqlCommand(sqlString, con);
returnValue = (int)command.ExecuteScalar();
}
}
/// <summary>
/// execute the sql expression according to the sql
/// </summary>
/// <param name="con"></param>
/// <param name="sqlString">procedure name or sql expression</param>
/// <param name="type" >execute style</param>
public static int ExecuteQuery(string sqlString, CommandType type)
{
using (SqlConnection con = SqlBase.GetConnection())
{
if (sqlString == null || sqlString == "")
{
throw new Exception(nullSqlExpress);
}
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
SqlCommand command = new SqlCommand();
command.Connection = con;
command.CommandText = sqlString;
command.CommandType = type;
return((int)command.ExecuteNonQuery());
}
}
/// <summary>
/// use dataset to help page the data
/// </summary>
/// <param name="sql">sql expression</param>
/// <param name="currentPage"></param>
/// <param name="pagesize"></param>
/// <param name="tableName"></param>
/// <param name="recordNo">the total number of the data collection</param>
/// <returns></returns>
public static DataView ExecuteDataView(string sql, int currentPage, int pagesize, string tableName)
{
if (sql == null || sql == "")
{
throw new Exception(nullSqlExpress);
}
using (SqlConnection con = SqlBase.GetConnection())
{
try
{
con.Open();
}
catch
{
throw new Exception(Sql_Connection_Err);
}
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);
}
}