2023年6月21日发(作者：)
C#动态创建SQL数据库（⼀）最近在做项⽬中要求能够要求动态添加数据库并建表。具体思路如下1 提供数据名，根据数据库创建数据库2 ⾃定数据库与数据表，提供数据表⾃定与数据类型创建表
创建sqlhelper类，⽤于数据库操作using System;using c;using ;using ent;using ;using ;/*******************************************************************
* Copyright (C) 版权所有* ⽂件名称:SqlHelper* 命名空间:TestRecentMenu* 创建时间:2018/12/18 14:26:01* 作 者: wangyonglai* 描 述：* 修改记录:* 修改⼈:* 版 本 号:v1.0.0**********************************************************************/namespace TestRecentMenu{ public static class SqlHelper { /// /// 使⽤锁防⽌多线程同时操作数据库表 /// private static readonly object sqlLock = new object(); /// /// SQL连接 /// private static SqlConnection connection; private static string connStr = "server=127.0.0.1; database=; user id=sa;password=Root123"; /// /// 创建SQL连接属性 /// public static SqlConnection Connection { get { try { if (connection == null)//如果没有创建连接，则先创建 { //从配置⽂件中获取SQL连接字段 //string connStr = tionStrings["ConnetcionNmae"].ToString(); connection = new SqlConnection(connStr);//创建连接 ();//打开连接 } else if ( == )//如果连接中断，则重现打开 { (); (); (); } else if ( == )//如果关闭，则打开 { (); } return connection; } catch (Exception ex) { if (connection != null) { (); e(); } //rrorLog("Connection" + e, ex); return null; } } } /// /// 重置连接 /// public static void ResetConnection() { if (connection != null) { (); e(); connection = null; } } /// /// 获取数据集 /// /// 执⾏字符串 /// public static DataSet GetDataSet(string str) { lock (sqlLock) { try { SqlDataAdapter sda = new SqlDataAdapter(str, Connection); DataSet ds = new DataSet(); (ds); return ds; } catch (Exception ex) { ResetConnection(); //rrorLog(str, ex); return null; } } } /// /// 获取数据集 /// /// /// /// /// public static DataSet GetDataSet(string str, params SqlParameter[] values) { lock (sqlLock) { try { SqlCommand cmd = new SqlCommand(); tion = Connection; dText = str; ge(values); SqlDataAdapter sda = new SqlDataAdapter(); Command = cmd; DataSet dt = new DataSet(); (dt); return dt; } catch (Exception ex) { ResetConnection(); //rrorLog(str, ex); return null; } } } /// /// 获取表格 /// /// 执⾏字符串 /// public static DataTable GetDataTable(string str) { return GetDataSet(str).Tables[0]; } /// /// 获取表格 /// /// 执⾏字符串 /// 参数值数组 /// public static DataTable GetDataTable(string str, params SqlParameter[] values) { return GetDataSet(str, values).Tables[0]; } /// /// 执⾏SQL语句 /// /// public static void ExecuteNonQuery(string str) { try { SqlCommand cmd = new SqlCommand(); tion = Connection; dType = ; dText = str; eNonQuery(); } catch {
} }
} /// /// 执⾏sql语句 /// /// /// public static bool ExecuteSql(string str) { lock (sqlLock) { SqlCommand cmd = new SqlCommand(); tion = Connection; SqlTransaction trans = ransaction(); try { if (tion != null && == ) { ction = trans; dType = ; dText = str; eNonQuery(); (); return true; } else { return false; } } catch (SqlException ex) { ck();//事物回滚 //ResetConnection(); //rrorLog(str, ex); return false; } } } /// /// 执⾏sql语句 /// /// /// /// public static bool ExecuteSql(string str, params SqlParameter[] values) { lock (sqlLock) { SqlCommand cmd = new SqlCommand(); tion = Connection; SqlTransaction trans = ransaction(); try { if (tion != null && == ) { ction = trans; dText = str; ge(values); eNonQuery(); (); return true; } } else { return false; } } catch (SqlException ex) { ck();//事物回滚 ResetConnection(); //rrorLog(str, ex); return false; } } } }}
编写调⽤函数/// /// 判断数据库是否存在 /// /// 数据库名称 /// public Boolean IsDBExist(string db) { string createDbStr = " select * from abases where name " + "= '" + db + "'"; DataTable dt = aTable(createDbStr); if ( > 0) { return true; } return false; } /// /// 判断数据库中指定表格是否存在 /// /// /// /// public Boolean IsTableExist(string db, string tb) { string createTbStr = "USE " + db + " select 1 from sysobjects where id =object_id('" + tb + "') and type = 'U'"; DataTable dt = aTable(createTbStr); if ( > 0) { return true; } return false; } /// /// 创建数据库 /// /// public void CreateDataBase(string db) { if (IsDBExist(db)) { throw new Exception("数据库已经存在！"); } else//不存在则创建 { string createDbStr = "Create DATABASE " + db; eNonQuery(createDbStr);
} } /// /// 创建数据库表 /// /// 数据库名 /// 表名 public void CreateDataTable(string db, string tb) { if (IsDBExist(db) == false) { throw new Exception("数据库不存在！"); } if (IsTableExist(db, tb)) { throw new Exception("数据库表已经存在！"); } else { string content = "usseId int IDENTITY(1,1) PRIMARY KEY ,userName nvarchar(50)"; string createTableStr = "USE " + db + " Create table " + tb + "(" + content + ")"; eNonQuery(createTableStr); } } 最后调⽤
private void button1_Click(object sender, EventArgs e) { try { CreateDataBase(database); } catch (Exception ex) { ine(e); } }private void button2_Click(object sender, EventArgs e) { CreateDataTable(database, "usertb"); }
2023年6月21日发(作者：)
C#动态创建SQL数据库（⼀）最近在做项⽬中要求能够要求动态添加数据库并建表。具体思路如下1 提供数据名，根据数据库创建数据库2 ⾃定数据库与数据表，提供数据表⾃定与数据类型创建表
创建sqlhelper类，⽤于数据库操作using System;using c;using ;using ent;using ;using ;/*******************************************************************
* Copyright (C) 版权所有* ⽂件名称:SqlHelper* 命名空间:TestRecentMenu* 创建时间:2018/12/18 14:26:01* 作 者: wangyonglai* 描 述：* 修改记录:* 修改⼈:* 版 本 号:v1.0.0**********************************************************************/namespace TestRecentMenu{ public static class SqlHelper { /// /// 使⽤锁防⽌多线程同时操作数据库表 /// private static readonly object sqlLock = new object(); /// /// SQL连接 /// private static SqlConnection connection; private static string connStr = "server=127.0.0.1; database=; user id=sa;password=Root123"; /// /// 创建SQL连接属性 /// public static SqlConnection Connection { get { try { if (connection == null)//如果没有创建连接，则先创建 { //从配置⽂件中获取SQL连接字段 //string connStr = tionStrings["ConnetcionNmae"].ToString(); connection = new SqlConnection(connStr);//创建连接 ();//打开连接 } else if ( == )//如果连接中断，则重现打开 { (); (); (); } else if ( == )//如果关闭，则打开 { (); } return connection; } catch (Exception ex) { if (connection != null) { (); e(); } //rrorLog("Connection" + e, ex); return null; } } } /// /// 重置连接 /// public static void ResetConnection() { if (connection != null) { (); e(); connection = null; } } /// /// 获取数据集 /// /// 执⾏字符串 /// public static DataSet GetDataSet(string str) { lock (sqlLock) { try { SqlDataAdapter sda = new SqlDataAdapter(str, Connection); DataSet ds = new DataSet(); (ds); return ds; } catch (Exception ex) { ResetConnection(); //rrorLog(str, ex); return null; } } } /// /// 获取数据集 /// /// /// /// /// public static DataSet GetDataSet(string str, params SqlParameter[] values) { lock (sqlLock) { try { SqlCommand cmd = new SqlCommand(); tion = Connection; dText = str; ge(values); SqlDataAdapter sda = new SqlDataAdapter(); Command = cmd; DataSet dt = new DataSet(); (dt); return dt; } catch (Exception ex) { ResetConnection(); //rrorLog(str, ex); return null; } } } /// /// 获取表格 /// /// 执⾏字符串 /// public static DataTable GetDataTable(string str) { return GetDataSet(str).Tables[0]; } /// /// 获取表格 /// /// 执⾏字符串 /// 参数值数组 /// public static DataTable GetDataTable(string str, params SqlParameter[] values) { return GetDataSet(str, values).Tables[0]; } /// /// 执⾏SQL语句 /// /// public static void ExecuteNonQuery(string str) { try { SqlCommand cmd = new SqlCommand(); tion = Connection; dType = ; dText = str; eNonQuery(); } catch {
} }
} /// /// 执⾏sql语句 /// /// /// public static bool ExecuteSql(string str) { lock (sqlLock) { SqlCommand cmd = new SqlCommand(); tion = Connection; SqlTransaction trans = ransaction(); try { if (tion != null && == ) { ction = trans; dType = ; dText = str; eNonQuery(); (); return true; } else { return false; } } catch (SqlException ex) { ck();//事物回滚 //ResetConnection(); //rrorLog(str, ex); return false; } } } /// /// 执⾏sql语句 /// /// /// /// public static bool ExecuteSql(string str, params SqlParameter[] values) { lock (sqlLock) { SqlCommand cmd = new SqlCommand(); tion = Connection; SqlTransaction trans = ransaction(); try { if (tion != null && == ) { ction = trans; dText = str; ge(values); eNonQuery(); (); return true; } } else { return false; } } catch (SqlException ex) { ck();//事物回滚 ResetConnection(); //rrorLog(str, ex); return false; } } } }}
编写调⽤函数/// /// 判断数据库是否存在 /// /// 数据库名称 /// public Boolean IsDBExist(string db) { string createDbStr = " select * from abases where name " + "= '" + db + "'"; DataTable dt = aTable(createDbStr); if ( > 0) { return true; } return false; } /// /// 判断数据库中指定表格是否存在 /// /// /// /// public Boolean IsTableExist(string db, string tb) { string createTbStr = "USE " + db + " select 1 from sysobjects where id =object_id('" + tb + "') and type = 'U'"; DataTable dt = aTable(createTbStr); if ( > 0) { return true; } return false; } /// /// 创建数据库 /// /// public void CreateDataBase(string db) { if (IsDBExist(db)) { throw new Exception("数据库已经存在！"); } else//不存在则创建 { string createDbStr = "Create DATABASE " + db; eNonQuery(createDbStr);
} } /// /// 创建数据库表 /// /// 数据库名 /// 表名 public void CreateDataTable(string db, string tb) { if (IsDBExist(db) == false) { throw new Exception("数据库不存在！"); } if (IsTableExist(db, tb)) { throw new Exception("数据库表已经存在！"); } else { string content = "usseId int IDENTITY(1,1) PRIMARY KEY ,userName nvarchar(50)"; string createTableStr = "USE " + db + " Create table " + tb + "(" + content + ")"; eNonQuery(createTableStr); } } 最后调⽤
private void button1_Click(object sender, EventArgs e) { try { CreateDataBase(database); } catch (Exception ex) { ine(e); } }private void button2_Click(object sender, EventArgs e) { CreateDataTable(database, "usertb"); }
发布评论