SQLBulkCopy using C#.NET

SQLBulkCopy using C#.NET Lets create a table to hold the data: CREATE TABLE [dbo].[tbl_BulkCopy] ( [ID] [int] IDENTITY(1,1) NOT NULL, [Column1] [varchar](100) NOT NULL, [Column2] [varchar](100) NOT NULL ); Now lets work on the C#.Net code behind file. We need to add a reference to the following namespace: using System.Data.SqlClient; using System.Configuration; System.Configuration namespace reference is required if you are trying to read config key value. Creating a sample method loadData() to implement the SqlBulkCopy functionality. void loadData() { string strConnString =   ConfigurationManager.ConnectionStrings[“DBConn”].ToString(); DataTable oDataTable = new DataTable(); oDataTable.Columns.Add(new DataColumn(“Column1”, typeof(string)); oDataTable.Columns.Add(new DataColumn(“Column2”, typeof(string)); for(int i=0;i<50000;i++) { DataRow oDataRow = oDataTable.NewRow(); oDataRow[“Column1”] = “column1_”+i.ToString(); oDataRow[“Column2”] = “column2_”+i.ToString(); oDataTable.Rows.Add(oDataRow); } using(SqlBulkCopy oSqlBulkCopy=new SqlBulkCopy(strConnString)) { oSqlBulkCopy.BatchSize=10000; oSqlBulkCopy.BulkCopyTimeout = 10000; oSqlBulkCopy.ColumnMappings.Add(“column1″,”column1”); oSqlBulkCopy.ColumnMappings.Add(“column2″,”column2″); oSqlBulkCopy.DestinationTableName=”tbl_BulkCopy”; oSqlBulkCopy.WriteToServer(oDataTable); } } loadData() method can be called on any Button click method to save the data.]]>

SQL Server 批量删除用户表的方法

DECLARE @Table NVARCHAR(30) DECLARE tmpCur CURSOR FOR SELECT name FROM sys.objects WHERE TYPE=’U’ AND name LIKE N’%’ OPEN tmpCur FETCH NEXT FROM tmpCur INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @sql VARCHAR(100) SELECT @sql = ‘drop table ‘ + @Table EXEC(@sql) FETCH NEXT FROM tmpCur INTO @Table END CLOSE tmpCur DEALLOCATE tmpCur ]]>