正文

C#创建数据库及附加数据库的操作方法

︶ㄣ米米素材网 メ
米米素材网V管理员 / 阅读
0708

这篇文章主要介绍了C#创建数据库及附加数据库的操作方法,涉及C#针对数据库常见的创建、添加、连接等操作技巧,需要的朋友可以参考下

本文实例讲述了C#创建数据库及附加数据库的操作方法。分享给大家供大家参考，具体如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
/// <summary>
/// 附加数据库方法
/// </summary>
/// <param name="strSql">连接数据库字符串，连接master系统数据库</param>
/// <param name="DataName">数据库名字</param>
/// <param name="strMdf">数据库文件MDF的路径</param>
/// <param name="strLdf">数据库文件LDF的路径</param>
/// <param name="path">安装目录</param>
private  void CreateDataBase( string strSql, string DataName, string strMdf, string strLdf, string path)
{
  SqlConnection myConn = new SqlConnection(strSql);
  String str = null ;
  try
  {
   str = " EXEC sp_attach_db @dbname='"+DataName+"',@filename1='"+strMdf+"',@filename2='"+strLdf+"'";
   SqlCommand myCommand = new SqlCommand(str, myConn);
   myConn.Open();
   myCommand.ExecuteNonQuery();
   MessageBox.Show("数据库安装成功！点击确定继续");//需Using System.Windows.Forms
  }
  catch(Exception e)
  {
   MessageBox.Show("数据库安装失败！" + e.Message+"\n\n"+"您可以手动附加数据");
   System.Diagnostics.Process.Start(path);//打开安装目录
  }
  finally
  {
   myConn.Close();
  }
}
public override void Install(System.Collections.IDictionary stateSaver)
{
  string server = this.Context.Parameters["server"];//服务器名称
  string uid = this.Context.Parameters["user"];//SQlServer用户名
  string pwd = this.Context.Parameters["pwd"];//密码
  string path = this.Context.Parameters["targetdir"];//安装目录
  string strSql = "server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=master";//连接数据库字符串
  string DataName = "JXC";//数据库名
  string strMdf = path + @"JXC.mdf";//MDF文件路径，这里需注意文件名要与刚添加的数据库文件名一样！
  string strLdf = path + @"jxc_log.ldf";//LDF文件路径
  base.Install(stateSaver);
  this.CreateDataBase(strSql, DataName, strMdf, strLdf, path);//开始创建数据库
}
/// <summary>
/// 测试连接
/// </summary>
/// <param name="serverName"></param>
/// <param name="dbName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
private SqlConnection TestConnection(string serverName, string dbName, string userName, string password)
{
    string connectionString = GetConnectionString(serverName, dbName, userName, password);
    SqlConnection connection = new SqlConnection(connectionString);
    try
    {
      if (connection.State != ConnectionState.Open)
      {
        connection.Open();
      }
      return connection;
    }
    catch
    {
      CloseConnection(connection);
      throw new InstallException("安装失败!\n数据库配置有误,请正确配置信息！");
    }
}
/// <summary>
/// 得到连接字符串
/// </summary>
/// <param name="serverName"></param>
/// <param name="dbName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <returns></returns>
private string GetConnectionString(string serverName, string dbName, string userName, string password)
{
    string connectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}";
    connectionString = string.Format(connectionString, serverName, dbName, userName, password);
    return connectionString;
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="serverName"></param>
/// <param name="dbName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
/// <param name="connection"></param>
/// <param name="stateSaver"></param>
public int CreateDataBase(SqlConnection connection)
{
    int result = -1;
    connection.ChangeDatabase("master");
    string createDBSql = @" if Exists(select 1 from sysdatabases where [name]=N'{0}')
        begin
        drop database {0}
        end
        GO
        CREATE DATABASE {0} ";
    createDBSql = string.Format(createDBSql, _dbName);
    //因为有Go在SQLCommand中不认识，所以以Go为分隔符取sql语句
    char[] split = new char[] { 'G', 'O' };
    string[] sqlList = createDBSql.Split(split);
    SqlCommand command = null;
    try
    {
      command = connection.CreateCommand();
      command.CommandType = System.Data.CommandType.Text;
      foreach (string sqlItem in sqlList)
      {
        if (sqlItem.Length > 2)
        {
          command.CommandText = sqlItem;
          result = command.ExecuteNonQuery();
        }
      }
      return result;
    }
    catch
    {
      CloseConnection(connection);
      command.Dispose();
      throw new InstallException("安装失败!\n数据库配置不正确!");
    }
}
/// <summary>
/// 分隔SQL语句
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private string[] splitSql(string sql)
{
    Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
    string[] sqlList = regex.Split(sql.ToUpper());
    return sqlList;
}

希望本文所述对大家C#程序设计有所帮助。