public void SchemaPrepare(BuildDbSchemaType buildtype = BuildDbSchemaType.Schema)
{
DataDbSchema dbs = new DataDbSchema(sqlcn);
bool wasOpen = false;
if (sqlcn.State == ConnectionState.Open)
{
wasOpen = true;
}
if (!wasOpen)
{
sqlcn.Open();
}
IEnumerable <DataRow> table = sqlcn.GetSchema("Tables").Rows.Cast <DataRow>().AsEnumerable().AsQueryable();
IEnumerable <DataRow> columns = sqlcn.GetSchema("Columns").Rows.Cast <DataRow>().AsEnumerable().AsQueryable();
IEnumerable <DataRow> index = sqlcn.GetSchema("IndexColumns").Rows.Cast <DataRow>().AsEnumerable().AsQueryable();
List <DbTable> dbTables = table
.Select(t =>
new DbTable()
{
TableName = t["TABLE_NAME"].ToString(),
DataDbColumns = new DbColumns()
{
List = columns.Where(c => t["TABLE_NAME"].Equals(c["TABLE_NAME"]))
.Select(k => new DbColumn
{
ColumnName = k["COLUMN_NAME"].ToString(),
RubricType = SqlNetType.SqlTypeToNet(k["DATA_TYPE"].ToString()),
MaxLength = (k["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value) ? Convert.ToInt32(k["CHARACTER_MAXIMUM_LENGTH"]) : -1,
isDBNull = (k["IS_NULLABLE"].ToString() == "YES")? true: false,
DbOrdinal = Convert.ToInt32(k["ORDINAL_POSITION"])
}).ToList()
},
DbPrimaryKey = index.Where(i =>
t["TABLE_NAME"].Equals(i["table_name"]))
.Where(it => columns
.Where(c => c["TABLE_NAME"].Equals(it["table_name"]) &&
c["COLUMN_NAME"].Equals(it["column_name"])).Any())
.Select(k => new DbColumn()
{
ColumnName = k["column_name"].ToString(),
isIdentity = (k["KeyType"].ToString() == "56") ? true : false,
isKey = true,
DbOrdinal = Convert.ToInt32(k["ordinal_position"]),
RubricType = SqlNetType.SqlTypeToNet(columns
.Where(c => c["TABLE_NAME"].Equals(k["table_name"]) &&
c["COLUMN_NAME"].Equals(k["column_name"]))
.First()["DATA_TYPE"].ToString())
}).ToArray()
}).ToList();
dbs.DataDbTables.AddRange(dbTables.ToList());
if (buildtype == BuildDbSchemaType.Schema)
{
DbHand.Schema = dbs;
}
else
{
DbHand.Temp = dbs;
}
}
public bool DataBulk(SpaceCard[] cards, string buforTable, BulkPrepareType prepareType = BulkPrepareType.None, BulkDbType dbType = BulkDbType.TempDB)
{
try
{
IMultemic deck = null;
if (cards.Any())
{
deck = cards.ElementAt(0).Collection;
if (_cn.State == ConnectionState.Closed)
{
_cn.Open();
}
try
{
if (dbType == BulkDbType.TempDB)
{
_cn.ChangeDatabase("tempdb");
}
if (!DbHand.Temp.DataDbTables.Have(buforTable) || prepareType == BulkPrepareType.Drop)
{
string createTable = "";
if (prepareType == BulkPrepareType.Drop)
{
createTable += "Drop table if exists [" + buforTable + "] \n";
}
createTable += "Create Table [" + buforTable + "] ( ";
foreach (MemberRubric column in deck.Rubrics.AsValues())
{
string sqlTypeString = "varchar(200)";
List <string> defineStr = new List <string>()
{
"varchar", "nvarchar", "ntext", "varbinary"
};
List <string> defineDec = new List <string>()
{
"decimal", "numeric"
};
int colLenght = column.RubricSize;
sqlTypeString = SqlNetType.NetTypeToSql(column.RubricType);
string addSize = (colLenght > 0) ? (defineStr.Contains(sqlTypeString)) ? (string.Format(@"({0})", colLenght)) :
(defineDec.Contains(sqlTypeString)) ? (string.Format(@"({0}, {1})", colLenght - 6, 6)) : "" : "";
sqlTypeString += addSize;
createTable += " [" + column.RubricName + "] " + sqlTypeString + ",";
}
createTable = createTable.TrimEnd(new char[] { ',' }) + " ) ";
SqlCommand createcmd = new SqlCommand(createTable, _cn);
createcmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
throw new SqlInsertException(ex.ToString());
}
if (prepareType == BulkPrepareType.Trunc)
{
string deleteData = "Truncate Table [" + buforTable + "]";
SqlCommand delcmd = new SqlCommand(deleteData, _cn);
delcmd.ExecuteNonQuery();
}
try
{
DataReader ndr = new DataReader(cards);
SqlBulkCopy bulkcopy = new SqlBulkCopy(_cn);
bulkcopy.DestinationTableName = "[" + buforTable + "]";
bulkcopy.WriteToServer(ndr);
}
catch (SqlException ex)
{
throw new SqlInsertException(ex.ToString());
}
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw new SqlInsertException(ex.ToString());
}
}