//public string InitImeiCiTypeTable() // { // //DialogResult result; //Messagebox所属于的类 // //result = MessageBox.Show("YesOrNo", "你确定要执行查询吗?", // // MessageBoxButtons.YesNo, MessageBoxIcon.Question); // //if (result == DialogResult.Yes)//Messagebox返回的值 // //{ // //CreateImeiCiTypeTable(); // //localdb = new DataClasses1DataContext(streamType.LocalConnString); // ////using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString)) // ////{ // //localdb.ExecuteCommand("delete from ciBVCI"); // //localdb.ExecuteCommand("delete from msIMEI"); // //} // //GC.Collect(); // //MessageBox.Show("OK"); // CreateTable(typeof(msIMEI)); // CreateTable(typeof(ciBVCI)); // //} // return streamType.LocalConnString; // } // //public string InitMlocationTable() // { // CreateTable(typeof(mLocatingType)); // //localdb = new DataClasses1DataContext(streamType.LocalConnString); // //using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString))//此处关键,数据插入到何处 // //{ // //mess.CommandTimeout = 6000;//sql连接超时的问题 //// DialogResult result; //Messagebox所属于的类 //// result = MessageBox.Show("YesOrNo", "你确定要执行查询吗?", //// MessageBoxButtons.YesNo, MessageBoxIcon.Question); //// if (result == DialogResult.Yes)//Messagebox返回的值 //// { //// CreateTable(typeof(mLocatingType)); ////// var typeName = "System.Data.Linq.SqlClient.SqlBuilder"; ////// var type = typeof(DataContext).Assembly.GetType(typeName); ////// var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod; ////// var metaTable = localdb.Mapping.GetTable(typeof(mLocatingType)); ////// var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); ////// //MessageBox.Show(sql.ToString ()); ////// string delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo.mLocatingType') and type = 'U') ////// drop table dbo.mLocatingType"; ////// localdb.ExecuteCommand(delSql.ToString()); ////// localdb.ExecuteCommand(sql.ToString()); //// //mess.ExecuteCommand("delete from mLocatingType"); //// //MessageBox.Show("OK"); //// } // //return mess.Connection.ConnectionString; // //} // //GC.Collect(); // //MessageBox.Show("OK"); // return streamType.LocalConnString; // } // private void CreateImeiCiTypeTable() // { // localdb = new DataClasses1DataContext(streamType.LocalConnString); // //using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString)) // //{ // var typeName = "System.Data.Linq.SqlClient.SqlBuilder"; // var type = typeof(DataContext).Assembly.GetType(typeName); // var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod; // #region 初始化《mlocating》 // /* // var metaTable = mess.Mapping.GetTable(typeof(mLocatingType)); // var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); // //MessageBox.Show(sql.ToString ()); // string delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo.mLocatingType') and type = 'U') // drop table dbo.mLocatingType"; // mess.ExecuteCommand(delSql.ToString()); // mess.ExecuteCommand(sql.ToString()); // * */ // #endregion // #region 初始化《imei库》和《ci覆盖类型库》的时候 执行 // /* // metaTable = mess.Mapping.GetTable(typeof(ciCoverType)); // sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); // delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo.ciCoverType') and type = 'U') // drop table dbo.ciCoverType"; // mess.ExecuteCommand(delSql.ToString()); // mess.ExecuteCommand(sql.ToString()); // metaTable = mess.Mapping.GetTable(typeof(imeiType)); // sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); // delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo.imeiType') and type = 'U') // drop table dbo.imeiType"; // mess.ExecuteCommand(delSql.ToString()); // mess.ExecuteCommand(sql.ToString()); // * */ // #endregion // var metaTable = localdb.Mapping.GetTable(typeof(msIMEI)); // var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); // string delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo.msIMEI') and type = 'U') // drop table dbo.msIMEI"; // localdb.ExecuteCommand(delSql.ToString()); // localdb.ExecuteCommand(sql.ToString()); // metaTable = localdb.Mapping.GetTable(typeof(ciBVCI)); // sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); // delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo.ciBVCI') and type = 'U') // drop table dbo.ciBVCI"; // localdb.ExecuteCommand(delSql.ToString()); // localdb.ExecuteCommand(sql.ToString()); // //MessageBox.Show("OK"); // } public static bool CreateTable(Type linqTableClass) { bool suc = true; string createtable = linqTableClass.Name; //MessageBox.Show(createtable); //混淆以后反射名称被改变出现问题 using (DataClasses1DataContext localdb = new DataClasses1DataContext(streamType.LocalConnString)) { try { var metaTable = localdb.Mapping.GetTable(linqTableClass); var typeName = "System.Data.Linq.SqlClient.SqlBuilder"; var type = typeof(DataContext).Assembly.GetType(typeName); var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod; var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }); string delSql = @"if exists (select 1 from sysobjects where id = object_id('dbo." + createtable + @"') and type = 'U') drop table dbo." + createtable; localdb.ExecuteCommand(delSql.ToString()); localdb.ExecuteCommand(sql.ToString()); } catch (Exception ex) { suc = false; MessageBox.Show(ex.ToString()); } } return suc; }
public string AlterPrimaryKey() { //try //{ localdb = new DataClasses1DataContext(streamType.LocalConnString); localdb.CommandTimeout = 0;//sql连接超时的问题 //Stopwatch sw = new Stopwatch(); //sw.Start(); //pk的问题 string sqlstr = @"select count(*) from sysobjects where parent_obj=object_id('IP_stream') and xtype='PK'"; int ti = int.Parse(localdb.ExecuteQuery<int>(sqlstr).ToDataTable().Rows[0][0].ToString()); //MessageBox.Show(ti.ToString()); if (ti < 1) { string sqlstr1 = @"alter table IP_stream alter column FileNum int not null"; localdb.ExecuteCommand(sqlstr1); Application.DoEvents(); string sqlstr2 = @"alter table IP_stream alter column PacketNum int not null"; localdb.ExecuteCommand(sqlstr2); Application.DoEvents(); string sqlstr3 = @"alter table IP_stream add constraint sid_pk primary key(FileNum,PacketNum)"; localdb.ExecuteCommand(sqlstr3); Application.DoEvents(); } CreateTable(typeof(msIMEI)); CreateTable(typeof(ciBVCI)); CreateTable(typeof(mLocatingType)); //sw.Stop(); //MessageBox.Show(sw.Elapsed.TotalSeconds.ToString()); //} //catch (Exception ex) //{ // MessageBox.Show(ex.ToString()); //} //finally //{ // localdb.Dispose(); //} return localdb.Connection.ConnectionString; }
private void shrinkDatabaseToolStripMenuItem_Click(object sender, EventArgs e) { try { //DUMP TRANSACTION [数据库名] WITH NO_LOG //BACKUP LOG [数据库名] WITH NO_LOG //DBCC SHRINKDATABASE([数据库名]) using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString)) { mess.ExecuteCommand("DUMP TRANSACTION [" + mess.Connection.Database + "] WITH NO_LOG"); mess.ExecuteCommand("BACKUP LOG [" + mess.Connection.Database + "] WITH NO_LOG"); mess.ExecuteCommand("DBCC SHRINKDATABASE([" + mess.Connection.Database + "])"); } MessageBox.Show("OK"); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void ImportimeiTypeFile() { // string dropsql = @" // IF EXISTS (SELECT * FROM sys.objects // WHERE object_id = OBJECT_ID(N'[dbo].[imeiType]') AND type in (N'U')) // DROP TABLE [dbo].[imeiType] // "; // string createsql = @" // CREATE TABLE [dbo].[imeiType]( // [imeiType_id] [decimal](18, 0) IDENTITY(1,1) NOT NULL, // [imei] [nvarchar](50) NULL, // [imeiFactory] [nvarchar](50) NULL, // [imeiModel] [nvarchar](500) NULL, // [imeiClass] [nvarchar](500) NULL // ) ON [PRIMARY] // "; handleTable.CreateTable(typeof(imeiType)); string insertsql = @" BULK INSERT imeiType FROM '" + streamType.imeiTypeFile + "' WITH ( FIRSTROW = 2,FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )"; using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString)) { try { //mess.ExecuteCommand(dropsql); //mess.ExecuteCommand(createsql); mess.ExecuteCommand(insertsql); } catch (Exception ex) { MessageBox.Show(ex.ToString()); string remotefilename = InputBox("与远程计算机的路径相匹配", "请选定imeiType.csv文件", @"F:\黑点项目\ips_setup\imeiType.csv"); string new_insertsql = @" BULK INSERT imeiType FROM '" + remotefilename + "' WITH ( FIRSTROW = 2,FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )"; mess.ExecuteCommand(new_insertsql); } } MessageBox.Show("OK"); }
private void ImportCiCoverType(string stattime) { string createsql = @" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ciCoverType]') AND type in (N'U')) DROP TABLE [dbo].[ciCoverType]"; string insertsql = @" SELECT IDENTITY(int, 1,1) AS ciCoverType_id,* into ciCoverType from (select lac+'-'+ci as lacCI,ci_name as ciName, available_pdch as ciAllocPDCH,use_pdch as ciUsePDCH from dbo.ciPdchBulk where stat_time='" + stattime + "') as a"; DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString); mess.ExecuteCommand(createsql); mess.ExecuteCommand(insertsql); MessageBox.Show("OK"); QueryTable("ciCoverType"); }
private void ciPdchBulk(string csvfile) { string dropsql = @" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ciPdchBulk]') AND type in (N'U')) DROP TABLE [dbo].[ciPdchBulk]"; string createsql = @" CREATE TABLE ciPdchBulk ( lac VARCHAR(32) null, ci VARCHAR(32) null, ci_name VARCHAR(32) null, stat_time VARCHAR(32) null, available_pdch VARCHAR(32) null, use_pdch VARCHAR(32) null, assignment_pdch_rate VARCHAR(32) null, )"; string insertsql = @" BULK INSERT ciPdchBulk FROM '" + csvfile + @"' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )"; DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString); mess.ExecuteCommand(dropsql); mess.ExecuteCommand(createsql); mess.ExecuteCommand(insertsql); MessageBox.Show("OK"); }
public void UpdateImeiType() { //Stopwatch sw = new Stopwatch(); //sw.Start(); int maxUser = 0; using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString)) maxUser = mess.msIMEI.Count(); //MessageBox.Show(maxUser.ToString()); using (SqlConnection con = new SqlConnection(streamType.LocalConnString)) { con.Open(); using (SqlTransaction tran = con.BeginTransaction()) { imeiTypeClass _imeiTypeClass = new imeiTypeClass(false); var newOrders = _imeiTypeClass.UpdateMsImeiCollection(); SqlBulkCopy bc = new SqlBulkCopy(con, //SqlBulkCopyOptions.CheckConstraints | //SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.KeepNulls, tran); bc.BulkCopyTimeout = 36000; bc.BatchSize = 1000; bc.DestinationTableName = "msIMEI"; bc.WriteToServer(newOrders.AsDataReader()); tran.Commit(); } con.Close(); } using (DataClasses1DataContext mess = new DataClasses1DataContext(streamType.LocalConnString)) mess.ExecuteCommand("delete from msIMEI where msIMEI_id<=" + maxUser); //Thread.Sleep(1); GC.Collect(); GC.Collect(); Application.DoEvents(); //sw.Stop(); //MessageBox.Show(sw.Elapsed.TotalSeconds.ToString()); }