//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());
}