private void InitDataBase(string dbName) { conStr = ConfigurationManager.AppSettings["ConString"]; SqlMapperUtil.GetOpenConnection(conStr); string exsitsDB = "select count(1) from sys.sysdatabases where name =@dbName"; int result = SqlMapperUtil.SqlWithParamsSingle <int>(exsitsDB, new { dbName = dbName }); if (result == 0) { string s1 = " create database [" + dbName + "]"; int ret = SqlMapperUtil.InsertUpdateOrDeleteSql(s1, null); } conStr = conStr.Replace("master", dbName); var StaticStructAndFn = Path.Combine(Directory.GetCurrentDirectory(), "StaticStructAndFn.tsql"); var sqls = File.ReadAllText(StaticStructAndFn); SqlServerHelper.ExecuteSql(sqls, conStr); string kjqjInsert = "delete dbo.kjqj where Projectid='{0}' " + " insert dbo.kjqj(ProjectID,CustomerCode,CustomerName,BeginDate,EndDate,KJDate)" + " select '{0}','{1}','{1}','{2}','{3}','{4}'"; SqlServerHelper.ExecuteSql(string.Format(kjqjInsert, dbName, _projectID, _beginDate, _endDate, _auditYear), conStr); }
private bool PD2SqlDB(string filepath, String dbName) { bool bRet = false; string filename = Path.GetFileNameWithoutExtension(filepath); try { var _ParadoxTable = new ParadoxReader.ParadoxTable(Path.GetDirectoryName(filepath), filename); var columns = _ParadoxTable.FieldNames; var fieldtypes = _ParadoxTable.FieldTypes; DataTable dt = new DataTable(); dt.TableName = Path.GetFileNameWithoutExtension(filepath);//_ParadoxTable.TableName; if (columns.Length == 0 || _ParadoxTable.RecordCount == 0) { return(bRet); } string tableName = dt.TableName; string typeName = "[dbo].[" + dt.TableName + "Type]"; string procName = "usp_insert" + dt.TableName; StringBuilder strSpt = new StringBuilder(string.Format("IF object_id('{0}') IS NOT NULL drop table {0}", tableName)); strSpt.AppendLine(" create table " + tableName + "(" + Environment.NewLine); StringBuilder strTypetv = new StringBuilder(string.Format("IF type_id('{0}') IS NOT NULL drop TYPE " + typeName, typeName)); strTypetv.AppendLine(" create TYPE " + typeName + " as TABLE(" + Environment.NewLine); string preProc = " IF EXISTS (SELECT * FROM dbo.sysobjects WHERE type = 'P' AND name = '" + procName + "') " + " BEGIN DROP Procedure " + procName + " END "; string createProc = " CREATE PROCEDURE " + procName + " (@tvpNewValues " + typeName + " READONLY)" + "as insert into " + tableName + " select * from @tvpNewValues "; for (int i = 0; i < columns.Length; i++) { string fieldName = columns[i]; DataColumn dc = new DataColumn(fieldName); ParadoxReader.ParadoxFieldTypes fieldType = fieldtypes[i].fType; switch (fieldType) { case ParadoxReader.ParadoxFieldTypes.BCD: case ParadoxReader.ParadoxFieldTypes.Number: case ParadoxReader.ParadoxFieldTypes.Currency: case ParadoxReader.ParadoxFieldTypes.Logical: case ParadoxReader.ParadoxFieldTypes.Short: strSpt.AppendLine(fieldName + " " + "decimal(19,3) null DEFAULT 0,"); strTypetv.AppendLine(fieldName + " " + "decimal(19,3) null DEFAULT 0,"); dc.DataType = typeof(System.Decimal); break; default: strSpt.AppendLine(fieldName + " " + "nvarchar(1000),"); strTypetv.AppendLine(fieldName + " " + "nvarchar(1000),"); dc.DataType = typeof(System.String); break; } dt.Columns.Add(dc); } string dtstring = strSpt.ToString().Substring(0, strSpt.Length - 3) + ") " + strTypetv.ToString().Substring(0, strTypetv.Length - 3) + ")"; string createDTSql = preProc + dtstring + " GO " + createProc; if (!string.IsNullOrEmpty(createDTSql)) { SqlServerHelper.ExecuteSql(createDTSql, conStr); } int idx = 0; foreach (var rec in _ParadoxTable.Enumerate()) { if (idx % 1000 == 0) { SqlServerHelper.ExecuteProcWithStruct(procName, conStr, typeName, dt); dt.Rows.Clear(); } DataRow dr = dt.NewRow(); for (int i = 0; i < _ParadoxTable.FieldCount; i++) { object OV = rec.DataValues[i]; if (!DBNull.Value.Equals(OV) && OV != null) { dr[_ParadoxTable.FieldNames[i]] = OV; } } dt.Rows.Add(dr); idx++; } _ParadoxTable.Dispose(); _ParadoxTable = null; SqlServerHelper.ExecuteProcWithStruct(procName, conStr, typeName, dt); dt.Dispose(); dt = null; } catch (Exception ex) { throw new Exception(string.Format("转换数据失败", filename), ex); } return(true); }