问题：给数据库中添加数据的sql文件没有了，怎么根据现在的数据库中表的数据导出insert语句呢？
解决：首先给网上查找了一些资料
sql2008 里面的所有数据类型
int,bigint,binary,bit,char,date,datetime,datetime2,datetimeoffset,decimal,float,geography,geometry,hierarchyid,image,money,nchar,ntext,numeric,nvarchar,nvarchar,real,smalldatetime,smallint,smallmoney,sql_variant,text,time,timestamp,tinyint,uniqueidentifier,varbinary,varbinary,varchar,varchar,xml,
select * from sys.all_objects --得到表中的列名与是否是自增的,当colstat为1的时候是自增的 select name,colstat from syscolumns where id=object_id(N'T_User') -- select name,DATA_TYPE,colstat from (select name,colstat from syscolumns where id=object_id(N'T_User')) as T1 join (select column_name,data_type from information_schema.columns where table_name = N'T_User' ) T2 on T1.name = T2.COLUMN_NAME --得到表中的列与类型 select column_name,data_type from information_schema.columns where table_name = N'T_User' select * from information_schema.columns where table_name = N'T_User' --获取当前数据库的用户表信息 select * from sysobjects where xtype='U' and category=0 select * from sys.tables select name from sys.tables --查询本地拥有的数据库 --1 select name from sys.databases where database_id>4 --2 select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
我做了一个WinForm窗口，效果如图：
cs代码部分（核心语句还是上面的sql语句）：
//返回已经有的用户创建的数据库 /// <summary> /// 返回已经有的用户创建的数据库 /// </summary> /// <returns>数据库名称列表</returns> private List<string> GetDataBase() { List<string> dataBaseList = new List<string>(); string connStr = "Data Source=.;Initial Catalog=master;Integrated Security=True"; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { //cmd.CommandText = "select name from sys.databases where database_id>4 ";使用这句返回的只有用户创建的数据库 cmd.CommandText = "select name from sys.databases "; SqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { dataBaseList.Add(Convert.ToString(dataReader[0])); } } } return dataBaseList; } //返回数据库中的表 /// <summary> ///返回数据库中的表 /// </summary> /// <param name="databaseName"></param> /// <returns></returns> private List<string> GetDataTable(string databaseName) { List<string> dataTableList = new List<string>(); string connStr = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True",databaseName); using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using(SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select name from sys.tables"; SqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { dataTableList.Add(Convert.ToString(dataReader[0])); } } } return dataTableList; } //返回数据库中某个表中的列 /// <summary> /// 返回数据库中某个表中的列 /// </summary> /// <param name="databaseName"></param> /// <param name="dataTableName"></param> /// <returns></returns> private List<string> GetColumns(string databaseName,string dataTableName) { List<string> columnsList = new List<string>(); string connStr = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", databaseName); using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format("select column_name,data_type from information_schema.columns where table_name = N'{0}'",dataTableName); // cmd.CommandText = string.Format(@"select name,DATA_TYPE,colstat from (select name,colstat from syscolumns where id=object_id(N'{0}')) as T1 // join (select column_name,data_type from information_schema.columns // where table_name = N'{0}' ) T2 // on T1.name = T2.COLUMN_NAME", dataTableName); SqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { columnsList.Add(string.Format("{0}",dataReader[0])); } } } return columnsList; } //返回数据库中某个表中的列与列的类型 /// <summary> /// 返回数据库中某个表中的列与列的类型 /// </summary> /// <param name="databaseName"></param> /// <param name="dataTableName"></param> /// <returns></returns> private Dictionary<string,string> GetColumnsInfo(string databaseName, string dataTableName) { Dictionary<string, string> columnsList = new Dictionary<string, string>(); string connStr = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", databaseName); using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format(@"select name,DATA_TYPE,colstat from (select name,colstat from syscolumns where id=object_id(N'{0}')) as T1 join (select column_name,data_type from information_schema.columns where table_name = N'{0}' ) T2 on T1.name = T2.COLUMN_NAME", dataTableName); SqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { columnsList.Add(Convert.ToString(dataReader[0]),Convert.ToString(dataReader[1])); } } } return columnsList; } //根据选中的表与选中的列生成Select命令 /// <summary> /// 根据选中的表与选中的列生成Select命令 /// </summary> /// <param name="dataTableName"></param> /// <param name="selectedColumnsList"></param> /// <returns></returns> public string BuildSelectCommand(string dataTableName, List<string> selectedColumnsList) { return "select " + string.Join(",", selectedColumnsList.ToArray()) + " from " + dataTableName; }
当然，上面的几个方法完全可以封装到一个类里面，这样使用起来就更加方便了。
于是，在原来的基础上，进行了改进，将有的部分封装成了类。这样层次性与重用性就更好了。
效果如下：
封装了一个DBHelper类与ItemHelper类，代码如下：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace ExportCommandBaseDB { class DBHelper { public static Dictionary<string, string> colInfo;//表的列与对应的类型 //执行命令 /// <summary> /// 执行命令 /// </summary> /// <param name="databaseName">数据库名称</param> /// <param name="commandText">命令语句</param> /// <returns>字符串列表</returns> public static List<string> ExecuteCommand(string databaseName, string commandText) { List<string> list = new List<string>(); string connStr = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", databaseName); using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { //cmd.CommandText = "select name from sys.databases where database_id>4 ";使用这句返回的只有用户创建的数据库 cmd.CommandText = commandText; SqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { list.Add(Convert.ToString(dataReader[0])); } } } return list; } //返回已经有的用户创建的数据库 /// <summary> /// 返回已经有的用户创建的数据库 /// </summary> /// <returns>数据库名称列表</returns> public static List<string> GetDataBase() { return ExecuteCommand("master", "select name from sys.databases"); } //返回数据库中的表 /// <summary> ///返回数据库中的表 /// </summary> /// <param name="databaseName"></param> /// <returns></returns> public static List<string> GetDataTable(string databaseName) { return ExecuteCommand(databaseName, "select name from sys.tables"); } //返回数据库中某个表中的列 /// <summary> /// 返回数据库中某个表中的列 /// </summary> /// <param name="databaseName"></param> /// <param name="dataTableName"></param> /// <returns></returns> public static List<string> GetColumns(string databaseName, string dataTableName) { string cmdText = string.Format("select column_name,data_type from information_schema.columns where table_name = N'{0}'", dataTableName); return ExecuteCommand(databaseName,cmdText); } //返回数据库中某个表中的列与列的类型 /// <summary> /// 返回数据库中某个表中的列与列的类型 /// </summary> /// <param name="databaseName">数据库名称</param> /// <param name="dataTableName">表的名称</param> /// <returns></returns> public static Dictionary<string, string> GetColumnsInfo(string databaseName, string dataTableName) { Dictionary<string, string> columnsList = new Dictionary<string, string>(); string connStr = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", databaseName); using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = string.Format(@"select name,DATA_TYPE,colstat from (select name,colstat from syscolumns where id=object_id(N'{0}')) as T1 join (select column_name,data_type from information_schema.columns where table_name = N'{0}' ) T2 on T1.name = T2.COLUMN_NAME", dataTableName); SqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { columnsList.Add(Convert.ToString(dataReader[0]), Convert.ToString(dataReader[1])); } } } colInfo = columnsList; return columnsList; } //根据选中的表与选中的列生成Select命令 /// <summary> /// 根据选中的表与选中的列生成Select命令 /// </summary> /// <param name="dataTableName">表的名称</param> /// <param name="selectedColumnsList">选中的列的列表</param> /// <returns>select语句命令</returns> public static string BuildSelectCommand(string dataTableName, List<string> selectedColumnsList) { return "select " + string.Join(",", selectedColumnsList.ToArray()) + " from " + dataTableName; } // 根据选中的列生成insert语句的格式 /// <summary> /// 根据选中的列生成insert语句的格式 /// </summary> /// <param name="dataTableName">表的名称</param> /// <param name="selectedItems">选中的列的列表</param> /// <returns>语句格式字符串</returns> public static string BuildInsertFormat(string dataTableName,List<string> selectedItems) { StringBuilder sb = new StringBuilder(); sb.Append(string.Format("insert {0}(", dataTableName)); sb.Append(string.Join(",", selectedItems.ToArray())); sb.Append(") values("); sb.Append(string.Join(",", BuildValueFormat(selectedItems).ToArray())); sb.Append(");"); return sb.ToString(); } //根据选中的列生成value部分的格式 /// <summary> /// 根据选中的列生成value部分的格式 /// </summary> /// <param name="selectedItems">选中的列的列表</param> /// <returns>value部分的格式</returns> private static List<string> BuildValueFormat(List<string> selectedItems) { List<string> listNumber = new List<string>(); listNumber.AddRange(new string[] { "tinyint", "smallint", "bigint", "int", "bit", "decimal", "decimal", "numeric" }); List<string> valueFormatList = new List<string>(); int i = 0; foreach (string item in selectedItems) { if (colInfo.ContainsKey(item)) { if (listNumber.Contains(colInfo[item])) { valueFormatList.Add("{" + i++ + "}"); } else { valueFormatList.Add("N'{" + i++ + "}'"); } } } return valueFormatList; } //完全可以只调用这一句话来导出。参数自己写就可以。 /// <summary> /// 根据选中的列导出插入内容的insert命令语句 /// </summary> /// <param name="databaseName">数据库名称</param> /// <param name="dataTableName">表名称</param> /// <param name="selectedColumnsList">选中的列的列表</param> /// <param name="format">insert语句的格式</param> /// <returns>insert所有的语句</returns> public static string ExportInsertCmd(string databaseName, string dataTableName, List<string> selectedColumnsList,string format) { StringBuilder sb = new StringBuilder(); string connStr = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", databaseName); using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = BuildSelectCommand(dataTableName, selectedColumnsList); SqlDataReader dataReader = cmd.ExecuteReader(); object[] values = new object[dataReader.FieldCount]; while (dataReader.Read()) { dataReader.GetValues(values); for (int i = 0; i < dataReader.FieldCount; i++) { if (String.IsNullOrEmpty(values[i].ToString())) { values[i] = "NULL"; } } sb.AppendLine(string.Format(format, values)); } } } //对生成N'NULL'的进行替换 sb = sb.Replace("N'NULL'", "NULL"); return sb.ToString(); } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows.Forms; using System.Collections; namespace ExportCommandBaseDB { class ItemHelper { /// <summary> /// 先清除comboBox中的所有项，然后将列表中的内容添加至其中,返回添加的项的个数 /// </summary> /// <param name="comboBox">ComboBox控件名称</param> /// <param name="list">内容列表</param> /// <returns>添加的项的个数</returns> public static int ClearAndFill(ComboBox comboBox, ICollection collection) { //为了解决能接受List<Object>类型的参数，也能接受List<string>类型的，还能接收其它类型的参数 //这里就使用了它们实现的统一的接口 IList //同理：CheckedListBox与ComboBox的继承自了ListControl类 comboBox.Items.Clear(); foreach (var item in collection) { //如果list是List<string>类型的，则可以这样使用comboBox.Items.AddRange(list.ToArray()); comboBox.Items.Add(item); } return collection.Count; } /// <summary> /// 先清除listBox中的所有项，然后将列表中的内容添加至其中,返回添加的项的个数 /// </summary> /// <param name="listBox">listBox控件名称</param> /// <param name="list">内容列表</param> /// <returns>添加的项的个数</returns> public static int ClearAndFill(ListBox listBox, ICollection collection) { //为了解决能接受List<Object>类型的参数，也能接受List<string>类型的，还能接收其它类型的参数 //这里就使用了它们实现的统一的接口 IList //同理：CheckedListBox与ComboBox的继承自了ListControl类 listBox.Items.Clear(); foreach (var item in collection) { //如果list是List<string>类型的，则可以这样使用comboBox.Items.AddRange(list.ToArray()); listBox.Items.Add(item); } return collection.Count; } /// <summary> /// 先清除CheckedListBox中的所有项，然后将列表中的内容添加至其中,返回添加的项的个数 /// </summary> /// <param name="chkListBox">CheckedListBox控件名称</param> /// <param name="list">内容列表</param> /// <returns>添加的项的个数</returns> public static int ClearAndFill(CheckedListBox chkListBox, ICollection collection) { chkListBox.Items.Clear(); foreach (var item in collection) { chkListBox.Items.Add(item); } return collection.Count; } /// <summary> /// 返回CheckedListBox选中的项的列表 /// </summary> /// <param name="chkListBox">CheckedListBox名称</param> /// <returns>勾选的项的列表</returns> public static List<string> CheckedItemsList(CheckedListBox chkListBox) { List<string> list = new List<string>(); foreach (object item in chkListBox.CheckedItems) { list.Add(item.ToString()); } return list; } //将CheckedListBox控件中的所有项全部选中 /// <summary> /// 将CheckedListBox控件中的所有项全部选中 /// </summary> /// <param name="chkListBox">CheckedListBox控件名称</param> public static void CheckedAll(CheckedListBox chkListBox) { for (int i = 0; i < chkListBox.Items.Count; i++) { chkListBox.SetItemChecked(i, true); } } //反选 /// <summary> /// 反选 /// </summary> /// <param name="chkListBox">CheckedListBox控件名称</param> public static void ReverseCheck(CheckedListBox chkListBox) { for (int i = 0; i < chkListBox.Items.Count; i++) { if (chkListBox.CheckedIndices.Contains(i)) { chkListBox.SetItemChecked(i, false); } else { chkListBox.SetItemChecked(i, true); } } } } }
然后在窗体中，就是简单的调用了。
可以去这里下载整个解决方案http://pan.baidu.com/share/link?shareid=565860261&uk=523599052