/// <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")); }