原文出处:
http://www.cnblogs.com/dxfcv222/archive/2007/06/28/798645.html
[.net] 将 datatable 存储到数据库
最近有这样的任务,需要将datatable 中的数据存储到数据库。以前有写过类似的解决方案,主要是利用 SqlCommand 对象结合 sql 查询语句实现。请看下面的代码片段:
上面的工作显得非常繁琐。
我经常想,datatable 和数据库中的表本质是一样的,存储应该是一个函数,一句话的
事情。
下面我就用一个函数 show 给大家看。
上面利用了。net 2。0 新加入的几个数据库操作类型。请务必先引入以下几个组建:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum
和如下名字空间:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer;
下面是测试代码:
1
//
创建表格
2 string creatingString = " if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ " + Table_Name + " ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ " + Table_Name + " ] " +
3 " CREATE TABLE [dbo].[ " + Table_Name + " ] ( " +
4 " [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , " +
5 " [account] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , " +
6 " [ShipName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , " +
7 " [LevelMove] [int] NULL , " +
8 " [ExpMove] [int] NULL , " +
9 " [LevelFight] [int] NULL , " +
10 " [ExpFight] [int] NULL , " +
11 " [LevelLoad] [int] NULL , " +
12 " [ExpLoad] [int] NULL , " +
13 " ) ON [PRIMARY] " ;
14
15 SqlCommand sqlCommand = new SqlCommand(creatingString, _databaseConnection as SqlConnection);
16 sqlCommand.ExecuteNonQuery();
2 string creatingString = " if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ " + Table_Name + " ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[ " + Table_Name + " ] " +
3 " CREATE TABLE [dbo].[ " + Table_Name + " ] ( " +
4 " [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , " +
5 " [account] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , " +
6 " [ShipName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , " +
7 " [LevelMove] [int] NULL , " +
8 " [ExpMove] [int] NULL , " +
9 " [LevelFight] [int] NULL , " +
10 " [ExpFight] [int] NULL , " +
11 " [LevelLoad] [int] NULL , " +
12 " [ExpLoad] [int] NULL , " +
13 " ) ON [PRIMARY] " ;
14
15 SqlCommand sqlCommand = new SqlCommand(creatingString, _databaseConnection as SqlConnection);
16 sqlCommand.ExecuteNonQuery();
1
//
插入记录到数据库
2
3 string insertString = " INSERT INTO " + Table_Name + " (name, account, ShipName, LevelMove, ExpMove, LevelFight, ExpFight, LevelLoad, ExpLoad) " +
4 " VALUES(@name, @account, @ShipName, @LevelMove, @ExpMove, @LevelFight, @ExpFight, @LevelLoad, @ExpLoad) " ;
5
6 SqlCommand sqlCommand = new SqlCommand(insertString, _databaseConnection as SqlConnection);
7
8 sqlCommand.Parameters.Add( " @name " , SqlDbType.VarChar, 50 ).Value = record.PlayerName;
9 sqlCommand.Parameters.Add( " @account " , SqlDbType.VarChar, 50 ).Value = record.PlayerAccount;
10 sqlCommand.Parameters.Add( " @ShipName " , SqlDbType.VarChar, 50 ).Value = record.ShipName;
11
12 sqlCommand.Parameters.Add( " @LevelMove " , SqlDbType.Int, 4 ).Value = record.LevelMove;
13 sqlCommand.Parameters.Add( " @ExpMove " , SqlDbType.Int, 4 ).Value = record.ExpMove;
14 sqlCommand.Parameters.Add( " @LevelFight " , SqlDbType.Int, 4 ).Value = record.LevelFight;
15 sqlCommand.Parameters.Add( " @ExpFight " , SqlDbType.Int, 4 ).Value = record.ExpFight;
16 sqlCommand.Parameters.Add( " @LevelLoad " , SqlDbType.Int, 4 ).Value = record.LevelLoad;
17 sqlCommand.Parameters.Add( " @ExpLoad " , SqlDbType.Int, 4 ).Value = record.ExpLoad;
18
19 sqlCommand.ExecuteNonQuery();
20 }
2
3 string insertString = " INSERT INTO " + Table_Name + " (name, account, ShipName, LevelMove, ExpMove, LevelFight, ExpFight, LevelLoad, ExpLoad) " +
4 " VALUES(@name, @account, @ShipName, @LevelMove, @ExpMove, @LevelFight, @ExpFight, @LevelLoad, @ExpLoad) " ;
5
6 SqlCommand sqlCommand = new SqlCommand(insertString, _databaseConnection as SqlConnection);
7
8 sqlCommand.Parameters.Add( " @name " , SqlDbType.VarChar, 50 ).Value = record.PlayerName;
9 sqlCommand.Parameters.Add( " @account " , SqlDbType.VarChar, 50 ).Value = record.PlayerAccount;
10 sqlCommand.Parameters.Add( " @ShipName " , SqlDbType.VarChar, 50 ).Value = record.ShipName;
11
12 sqlCommand.Parameters.Add( " @LevelMove " , SqlDbType.Int, 4 ).Value = record.LevelMove;
13 sqlCommand.Parameters.Add( " @ExpMove " , SqlDbType.Int, 4 ).Value = record.ExpMove;
14 sqlCommand.Parameters.Add( " @LevelFight " , SqlDbType.Int, 4 ).Value = record.LevelFight;
15 sqlCommand.Parameters.Add( " @ExpFight " , SqlDbType.Int, 4 ).Value = record.ExpFight;
16 sqlCommand.Parameters.Add( " @LevelLoad " , SqlDbType.Int, 4 ).Value = record.LevelLoad;
17 sqlCommand.Parameters.Add( " @ExpLoad " , SqlDbType.Int, 4 ).Value = record.ExpLoad;
18
19 sqlCommand.ExecuteNonQuery();
20 }
上面的工作显得非常繁琐。
我经常想,datatable 和数据库中的表本质是一样的,存储应该是一个函数,一句话的
事情。
下面我就用一个函数 show 给大家看。
1
public
static
void
Table2Db(DataTable dt, SqlConnection connection,
string
dbName)
2 {
3 Debug.Assert(dt != null);
4 Debug.Assert(connection != null);
5 Debug.Assert(connection.State == ConnectionState.Open);
6 Debug.Assert(dbName != null);
7 Debug.Assert(dbName != string.Empty);
8
9 Server server = new Server(new ServerConnection(connection));
10 Database db = server.Databases[dbName];
11 if (db == null)
12 {
13 db = new Database(server, dbName);
14 db.Create();
15 }
16
17 Table table = db.Tables[dt.TableName];
18 if (table == null)
19 {
20 table = new Table(db, dt.TableName);
21 foreach (DataColumn dc in dt.Columns)
22 {
23 Column column = new Column(table, dc.ColumnName);
24 column.DataType = DataType.VarChar(50);
25 table.Columns.Add(column);
26 }
27 table.Create();
28 }
29
30 using (SqlBulkCopy copy = new SqlBulkCopy(connection))
31 {
32 copy.DestinationTableName = string.Format("[{0}]", dt.TableName);
33 copy.WriteToServer(dt);
34 }
35
36 Debug.Assert(server != null);
37 Debug.Assert(table != null);
38 }
2 {
3 Debug.Assert(dt != null);
4 Debug.Assert(connection != null);
5 Debug.Assert(connection.State == ConnectionState.Open);
6 Debug.Assert(dbName != null);
7 Debug.Assert(dbName != string.Empty);
8
9 Server server = new Server(new ServerConnection(connection));
10 Database db = server.Databases[dbName];
11 if (db == null)
12 {
13 db = new Database(server, dbName);
14 db.Create();
15 }
16
17 Table table = db.Tables[dt.TableName];
18 if (table == null)
19 {
20 table = new Table(db, dt.TableName);
21 foreach (DataColumn dc in dt.Columns)
22 {
23 Column column = new Column(table, dc.ColumnName);
24 column.DataType = DataType.VarChar(50);
25 table.Columns.Add(column);
26 }
27 table.Create();
28 }
29
30 using (SqlBulkCopy copy = new SqlBulkCopy(connection))
31 {
32 copy.DestinationTableName = string.Format("[{0}]", dt.TableName);
33 copy.WriteToServer(dt);
34 }
35
36 Debug.Assert(server != null);
37 Debug.Assert(table != null);
38 }
上面利用了。net 2。0 新加入的几个数据库操作类型。请务必先引入以下几个组建:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum
和如下名字空间:
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer;
下面是测试代码:
1
2 // ------------------------------------------------------------------
3
4
5
6 // 连接串形成
7
8
9 public static string GetConnectionString( string server,
10 string database,
11 string userName,
12 string password)
13 {
14
15 return string.Format("server={0};uid={1};pwd={2};database={3}",
16 server,
17 userName,
18 password,
19 database);
20 }
21
22
23 // ------------------------------------------------------------------
24
25
26
27
28 static void Main( string [] args)
29 {
30
31
32
33
34 //------------------------------------------------------------------
35
36 //表格创建
37
38
39 DataTable dt = new DataTable("1");
40
41 dt.Columns.Add("1");
42 dt.Columns.Add("2");
43 dt.Columns.Add("3");
44 dt.Columns.Add("4");
45
46
47
48 for (int i = 0; i < 1000; ++i)
49 {
50 DataRow dr = dt.NewRow();
51
52
53 dr[0] = dr[1] = dr[2] = dr[3] = i;
54
55
56 dt.Rows.Add(dr);
57
58 }
59
60
61 //------------------------------------------------------------------
62
63 //存储到数据库
64
65
66 SqlConnection connection = new SqlConnection(GetConnectionString("192.168.1.79", "nx_server_hotdancing_dxf", "sa", "abc"));
67 connection.Open();
68
69 try
70 {
71 Table2Db(dt, connection, "nx_server_hotdancing_dxf");
72 }
73 catch(Exception)
74 {
75 //做点什么?
76 }
77
78 connection.Close();
79
80
81
82 //------------------------------------------------------------------
83
84
85
86 }
87
2 // ------------------------------------------------------------------
3
4
5
6 // 连接串形成
7
8
9 public static string GetConnectionString( string server,
10 string database,
11 string userName,
12 string password)
13 {
14
15 return string.Format("server={0};uid={1};pwd={2};database={3}",
16 server,
17 userName,
18 password,
19 database);
20 }
21
22
23 // ------------------------------------------------------------------
24
25
26
27
28 static void Main( string [] args)
29 {
30
31
32
33
34 //------------------------------------------------------------------
35
36 //表格创建
37
38
39 DataTable dt = new DataTable("1");
40
41 dt.Columns.Add("1");
42 dt.Columns.Add("2");
43 dt.Columns.Add("3");
44 dt.Columns.Add("4");
45
46
47
48 for (int i = 0; i < 1000; ++i)
49 {
50 DataRow dr = dt.NewRow();
51
52
53 dr[0] = dr[1] = dr[2] = dr[3] = i;
54
55
56 dt.Rows.Add(dr);
57
58 }
59
60
61 //------------------------------------------------------------------
62
63 //存储到数据库
64
65
66 SqlConnection connection = new SqlConnection(GetConnectionString("192.168.1.79", "nx_server_hotdancing_dxf", "sa", "abc"));
67 connection.Open();
68
69 try
70 {
71 Table2Db(dt, connection, "nx_server_hotdancing_dxf");
72 }
73 catch(Exception)
74 {
75 //做点什么?
76 }
77
78 connection.Close();
79
80
81
82 //------------------------------------------------------------------
83
84
85
86 }
87