#region 创建数据库方法 public static string BuildConnectionString(string workstationId,
string userId ,
string pwd,
string dbname
) {
string fmt="workstation id={0};user id={1};pwd={2};initial catalog={3}";
string connectionString=string.Format(fmt,
workstationId,//服务器名称
userId,//用户名
pwd,
dbname);//口令 return connectionString;
}
private static void ExecuteSqlScript(string connectionString ,string sqlScript) {
SqlConnection sqlConnection=new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand=new SqlCommand();
sqlCommand.Connection=sqlConnection; sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText=sqlScript;
try {
sqlCommand.ExecuteNonQuery();
}
finally {
sqlConnection.Close();
sqlCommand.Dispose();
sqlConnection.Dispose();
}
} public static void DropDatabase(string connectionString,string dbname) {
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("use master ");
sb.AppendFormat(" IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'{0}') ",dbname);
sb.AppendFormat(" DROP DATABASE [{0}]",dbname);
ExecuteSqlScript(connectionString,sb.ToString());
} /// <summary>
/// 创建数据库,使用缺省的参数
/// </summary>
/// <param name="connectionString">连接到数据库服务器的连接字符串</param>
/// <param name="dbname">创建的数据库名称</param>
public static void CreateDatabase(string connectionString,string dbname){
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("use master ");
sb.AppendFormat("Create DataBase {0} ",dbname);
ExecuteSqlScript(connectionString,sb.ToString());
} /// <summary>
/// 根据指定的参数,创建数据库
/// </summary>
/// <param name="connectionString">连接到数据库服务器的连接字符串</param>
/// <param name="dbName">创建的数据库名称</param>
/// <param name="dbFilePathAndName">创建的数据库主文件保存的位置,如:C:\\testdb.mdf</param>
/// <param name="dbLogFilePathAndName">创建的数据库日志文件保存的位置,如:C:\\testdb_log.ldf</param>
public static void CreateDatabase(
string connectionString,
string dbName,
string dbFilePathAndName,
string dbLogFilePathAndName
){
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("use master ");
sb.AppendFormat("Create DataBase {0} ON (NAME = N'{0}', FILENAME = N'{1}' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'{0}_log', FILENAME = N'{2}' , FILEGROWTH = 10%)",dbName,dbFilePathAndName,dbLogFilePathAndName); ExecuteSqlScript(connectionString,sb.ToString());
}
#endregion
string userId ,
string pwd,
string dbname
) {
string fmt="workstation id={0};user id={1};pwd={2};initial catalog={3}";
string connectionString=string.Format(fmt,
workstationId,//服务器名称
userId,//用户名
pwd,
dbname);//口令 return connectionString;
}
private static void ExecuteSqlScript(string connectionString ,string sqlScript) {
SqlConnection sqlConnection=new SqlConnection(connectionString);
sqlConnection.Open();
SqlCommand sqlCommand=new SqlCommand();
sqlCommand.Connection=sqlConnection; sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText=sqlScript;
try {
sqlCommand.ExecuteNonQuery();
}
finally {
sqlConnection.Close();
sqlCommand.Dispose();
sqlConnection.Dispose();
}
} public static void DropDatabase(string connectionString,string dbname) {
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("use master ");
sb.AppendFormat(" IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'{0}') ",dbname);
sb.AppendFormat(" DROP DATABASE [{0}]",dbname);
ExecuteSqlScript(connectionString,sb.ToString());
} /// <summary>
/// 创建数据库,使用缺省的参数
/// </summary>
/// <param name="connectionString">连接到数据库服务器的连接字符串</param>
/// <param name="dbname">创建的数据库名称</param>
public static void CreateDatabase(string connectionString,string dbname){
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("use master ");
sb.AppendFormat("Create DataBase {0} ",dbname);
ExecuteSqlScript(connectionString,sb.ToString());
} /// <summary>
/// 根据指定的参数,创建数据库
/// </summary>
/// <param name="connectionString">连接到数据库服务器的连接字符串</param>
/// <param name="dbName">创建的数据库名称</param>
/// <param name="dbFilePathAndName">创建的数据库主文件保存的位置,如:C:\\testdb.mdf</param>
/// <param name="dbLogFilePathAndName">创建的数据库日志文件保存的位置,如:C:\\testdb_log.ldf</param>
public static void CreateDatabase(
string connectionString,
string dbName,
string dbFilePathAndName,
string dbLogFilePathAndName
){
System.Text.StringBuilder sb=new System.Text.StringBuilder();
sb.Append("use master ");
sb.AppendFormat("Create DataBase {0} ON (NAME = N'{0}', FILENAME = N'{1}' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'{0}_log', FILENAME = N'{2}' , FILEGROWTH = 10%)",dbName,dbFilePathAndName,dbLogFilePathAndName); ExecuteSqlScript(connectionString,sb.ToString());
}
#endregion
/// <summary>
/// Private routine allowed only by this base class, it automates the task
/// of building a SqlCommand object designed to obtain a return value from
/// the stored procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>
/// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>
/// <returns>Newly instantiated SqlCommand instance</returns>
private static SqlCommand BuildIntCommand(SqlConnection connection,
string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters ); command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null )); return command;
}
/// <summary>
/// Builds a SqlCommand designed to return a SqlDataReader, and not
/// an actual integer value.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <returns></returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection,
string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, connection );
command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
} return command;
}
#endregion
using System;
using NUnit.Framework;namespace SkyDev.Data
{
[TestFixture]
public class TestSqlHelper
{
string _connectionString;
//安装测试用例,完成初始化操作
[SetUp]
public void SetUp()
{
_connectionString=SqlHelper.BuildConnectionString("SKYDEV","sa","","master");
} //测试结束完成清理操作
[TearDown]
public void TearDown()
{
}
[Test]
public void TestDropDatabase() {
SqlHelper.DropDatabase(_connectionString,"TESTDB");
} [Test]
public void TestCreateDatabase() {
SqlHelper.CreateDatabase(_connectionString,"TESTDB");
} [Test]
public void TestDropDatabase2() {
SqlHelper.DropDatabase(_connectionString,"TESTDB2");
} [Test]
public void TestCreateDatabase2() {
SqlHelper.CreateDatabase(_connectionString,"TESTDB2","c:\\testdb.mdf","c:\\testdb_log.ldf");
}
}
}
private static SqlCommand BuildIntCommand(SqlConnection connection,
string storedProcName, IDataParameter[] parameters)
写死了存储过程参数类型及个数的个人认为没有通用性,不能被封装
为什么要用你的
string storedProcName, IDataParameter[] parameters) 只是被上面调用存储过程的方法调用,看仔细就知道楼猪写的还是很不错的!
Microsoft.ApplicationBlocks.Data.dll
希望大家多多指教。