/// <summary>
/// 根据sql语句执行增删改操作,返回受影响的行数
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns></returns>
public static int ExcuteBySql(string strSql)
{
int i = 0;
string strConn = SqlDataHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(strConn))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCommand command = new SqlCommand(strSql, connection);
command.CommandTimeout = iTimeOut;
i = command.ExecuteNonQuery();
}
return(i);
}
/// <summary>
/// 根据sql语句得到 DataTable
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>DataTable</returns>
public static DataTable GetDatatableBySql(string strSql)
{
DataTable dt = new DataTable();
string strConn = SqlDataHelper.GetConnectionString();
using (SqlConnection connection = new SqlConnection(strConn))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCommand command = new SqlCommand(strSql, connection);
command.CommandTimeout = iTimeOut;
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(dt);
}
return(dt);
}
/// <summary>
/// Backup database
/// </summary>
/// <param name="fileName">Destination file name</param>
public static void Backup(string fileName)
{
//TODO SQL Server only now
string sqlConnectionString = SqlDataHelper.GetConnectionString("CRMSqlConnection");
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
string dbName = SqlDataHelper.GetDatabaseName(sqlConnectionString);
string commandText = string.Format(
"BACKUP DATABASE [{0}] TO DISK = '{1}' WITH FORMAT",
dbName,
fileName);
DbCommand dbCommand = new SqlCommand(commandText, conn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
dbCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Restore database
/// </summary>
/// <param name="fileName">Target file name</param>
public static void RestoreBackup(string fileName)
{
//TODO SQL Server only now
string sqlConnectionString = SqlDataHelper.GetConnectionString("CRMSqlConnection");
string masterConnectionString = SqlDataHelper.GetMasterConnectionString(sqlConnectionString);
using (SqlConnection conn = new SqlConnection(masterConnectionString))
{
string dbName = SqlDataHelper.GetDatabaseName(sqlConnectionString);
string commandText = string.Format(
"DECLARE @ErrorMessage NVARCHAR(4000)\n" +
"ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\n" +
"BEGIN TRY\n" +
"RESTORE DATABASE [{0}] FROM DISK = '{1}' WITH REPLACE\n" +
"END TRY\n" +
"BEGIN CATCH\n" +
"SET @ErrorMessage = ERROR_MESSAGE()\n" +
"END CATCH\n" +
"ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE\n" +
"IF (@ErrorMessage is not NULL)\n" +
"BEGIN\n" +
"RAISERROR (@ErrorMessage, 16, 1)\n" +
"END",
dbName,
fileName);
DbCommand dbCommand = new SqlCommand(commandText, conn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
dbCommand.ExecuteNonQuery();
}
//clear all pools
SqlConnection.ClearAllPools();
}
public static string GetConnectionString()
{
return(SqlDataHelper.GetConnectionString("CRMSqlConnection"));
}