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