public DataCommand GetDataCommand(string defaultDb, DbUtility actualData)
{
//DbCommand dbCommand = GetDbCommand(actualData);
DbCommand dbCommand = GetDbCommandFromCommandText(actualData);
string database = Database ?? defaultDb;
return new DataCommand(database, dbCommand);
}
public void DropSridConstraint()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
DropSridConstraint(conn, TableSchema, Table, GeometryColumn);
}
}
protected virtual string InsertClause(IDbCommand cmd)
{
List <string> sets = new List <string>();
//Columnnames
DataColumnCollection dcc = GetSchemaTable().Columns;
foreach (DataColumn dc in dcc)
{
sets.Add(string.Format(" [{0}]", dc.ColumnName));
}
String columNames = string.Format("({0}, [{1}])", String.Join(",", sets.ToArray()).Trim(), GeometryColumn);
sets.Clear();
//Parameter
foreach (DataColumn dc in dcc)
{
IDataParameter param = null;
sets.Add(string.Format(" {0}", ParamForColumn(dc, out param)));
cmd.Parameters.Add(param);
}
//Geometry
sets.Add(string.Format("{0}", string.Format(GeomFromWkbFormatString, "@PGeo")));
cmd.Parameters.Add(DbUtility.CreateParameter <byte[]>("@PGeo", ParameterDirection.Input));
return(String.Format("{0} VALUES({1})", columNames, string.Join(",", sets.ToArray()).Trim()));
}
private IConfigurationSettingItem Parse(string fileName, string xpath)
{
var setting = new XmlInstanceSetting();
DbUtility.ParseDataDirectory(ref fileName);
setting.FileName = fileName;
if (!File.Exists(setting.FileName))
{
throw new FileNotFoundException(SR.GetString(SRKind.FileNotFound, setting.FileName), setting.FileName);
}
var xmlDoc = new XmlDocument();
xmlDoc.Load(setting.FileName);
var connNode = xmlDoc.SelectSingleNode(xpath);
if (connNode == null)
{
throw new XPathException(xpath);
}
setting.ProviderName = connNode.GetAttributeValue("providerName");
setting.ProviderType = connNode.GetAttributeValue("providerType");
setting.DatabaseType = Type.GetType(connNode.GetAttributeValue("databaseType"), false, true);
setting.ConnectionString = ConnectionStringHelper.GetConnectionString(connNode.GetAttributeValue("connectionString"));
return(setting);
}
private IConfigurationSettingItem Parse(string fileName)
{
fileName = DbUtility.ResolveFullPath(fileName);
if (!File.Exists(fileName))
{
throw new FileNotFoundException(SR.GetString(SRKind.FileNotFound, fileName), fileName);
}
var content = File.ReadAllText(fileName);
var dict = (IDictionary <string, object>) new JsonSerializer().Deserialize <object>(content);
var setting = new JsonInstanceSetting
{
FileName = fileName,
ProviderName = dict.TryGetValue("providerName", () => string.Empty)?.ToString(),
ProviderType = dict.TryGetValue("providerType", () => string.Empty)?.ToString()
};
if (dict.TryGetValue("databaseType", out object databaseType))
{
setting.DatabaseType = Type.GetType((string)databaseType);
}
if (dict.TryGetValue("connectionString", out object connectionString))
{
setting.ConnectionString = ConnectionStringHelper.GetConnectionString((string)connectionString);
}
return(setting);
}
/// <summary>
/// 批量将一组实体对象创建到库中。
/// </summary>
/// <param name="entities">一组要插入实体对象。</param>
/// <param name="batchSize">每一个批次写入的实体数量。默认为 1000。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
public virtual void BatchCreate(IEnumerable <TEntity> entities, int batchSize = 1000, Action <int> completePercentage = null)
{
Guard.ArgumentNull(entities, "entities");
var batcher = Database.Provider.GetService <IBatcherProvider>();
if (batcher == null)
{
throw new EntityPersistentException(SR.GetString(SRKind.NotSupportBatcher), null);
}
var syntax = Database.Provider.GetService <ISyntaxProvider>();
var rootType = GetEntityType().GetRootType();
var tableName = string.Empty;
if (Environment != null)
{
tableName = DbUtility.FormatByQuote(syntax, Environment.GetVariableTableName(rootType));
}
else
{
var metadata = EntityMetadataUnity.GetEntityMetadata(rootType);
tableName = DbUtility.FormatByQuote(syntax, metadata.TableName);
}
batcher.Insert(Database, entities, tableName, batchSize, completePercentage);
}
private bool ExecuteSqlScript(IEnumerable <string> sqlScriptFilePaths)
{
bool connetSuccess = true;
try
{
DbUtility dbUtility = DbUtilityCreator.GetDefaultDbUtility();
if (dbUtility.DbProviderType == DbProviderType.MySql)
{
foreach (var sqlScriptFilePath in sqlScriptFilePaths)
{
MySqlDataBaseHelper.ExecuteMySqlScriptFile(sqlScriptFilePath, dbUtility.ConnectionString);
}
}
else
{
//TODO:处理其它数据库执行脚本代码
}
}
catch (Exception)
{
connetSuccess = false;
throw;
}
return(connetSuccess);
}
public GetCurrentDataBaseLoginInfoResponse GetCurrentDataBaseLoginInfoAndConnetStatus()
{
GetCurrentDataBaseLoginInfoResponse response = new GetCurrentDataBaseLoginInfoResponse();
try
{
DbUtility dbUtility = DbUtilityCreator.GetDefaultDbUtility();
if (null != dbUtility)
{
response.Ip = dbUtility.SqlconModel.DBIP;
response.Account = dbUtility.SqlconModel.DBUser;
response.Password = dbUtility.SqlconModel.DBPasswd;
if (dbUtility.TestConnection())
{
response.DataBaseConnecting = true;
}
else
{
response.DataBaseConnecting = false;
}
}
}
catch (Exception ex)
{
string message = "操作失败。" + ex.Message;
response.ResponseSucceed = false;
response.Message = message;
LoggingFactory.GetLogger().WriteDebugLogger(message);
}
return(response);
}
private bool TestConnetDataBaseButNotSave(string ip, string account, string password)
{
bool connetSuccess = true;
try
{
DbUtility dbUtility = DbUtilityCreator.GetDefaultDbUtility();
DbUtility dbUtilityToTest = null;
SQLConnModel sqlConnModel = dbUtility.SqlconModel;
sqlConnModel.DBIP = ip;
sqlConnModel.DBUser = account;
sqlConnModel.DBPasswd = password;
dbUtilityToTest = new DbUtility(sqlConnModel, dbUtility.DbProviderType);
connetSuccess = dbUtilityToTest.TestConnection();
if (connetSuccess)
{
this.sqlConnModel = sqlConnModel;
}
}
catch (Exception)
{
connetSuccess = false;
throw;
}
return(connetSuccess);
}
private bool CreataDataSqlScript(IEnumerable <string> sqlScriptFilePaths)
{
bool connetSuccess = true;
try
{
DbUtility dbUtility = DbUtilityCreator.GetDefaultDbUtility();
if (dbUtility.DbProviderType == DbProviderType.MySql)
{
//创建数据库脚本时，还没有数据库存在，所以
//connectionString不能存在数据库名，否则执行脚本不成功。
string connectionString = dbUtility.ConnectionString.Replace(dbUtility.SqlconModel.DBName, "");
foreach (var sqlScriptFilePath in sqlScriptFilePaths)
{
MySqlDataBaseHelper.ExecuteMySqlScriptFile(sqlScriptFilePath, connectionString);
}
}
else
{
//TODO:处理其它数据库执行脚本代码
}
}
catch (Exception)
{
connetSuccess = false;
throw;
}
return(connetSuccess);
}
private SqlCommand User_Update()
{
//this is executed from claim.save in authorize.aspx
SqlCommand command = new SqlCommand();
command.CommandText = "User_Update";
command.CommandType = CommandType.StoredProcedure;
SqlParameterCollection parameters = command.Parameters;
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.Int, 8, "@UserID",
"UserID", ParameterDirection.Input);
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.NVarChar, 250, "@Email",
"Email", ParameterDirection.Input);
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.NVarChar, 150, "@FirstName",
"FirstName", ParameterDirection.Input);
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.NVarChar, 50, "@LastName",
"LastName", ParameterDirection.Input);
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.NVarChar, 50, "@Password",
"Password", ParameterDirection.Input);
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.Int, 4, "@SecurityLevel",
"SecurityLevel", ParameterDirection.Input);
DbUtility.AddParameterForDataAdapterCall(parameters,
SqlDbType.NVarChar, 50, "@Store",
"Store", ParameterDirection.Input);
return(command);
}
public dsUser UserByID(int id)
{
dsUser tempDS = new dsUser();
SqlConnection connection = new SqlConnection(
Configuration.GetValue("DSN"));
connection.Open();
SqlCommand command = new SqlCommand();
command.CommandText = "FindUserByID";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
SqlParameterCollection parameters = command.Parameters;
DbUtility.AddParameter(parameters,
SqlDbType.Int, 50, "@id", id);
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(tempDS);
connection.Close();
return(tempDS);
}
protected virtual String ParamForColumn(DataColumn dc, out IDataParameter param)
{
String paramName = ParamForColumn(dc);
param = DbUtility.CreateParameter(paramName, dc.DataType, ParameterDirection.Input);
return(paramName);
}
//public static int GetNoticeListForEditPageCount(SystemRollEnum roll, string organizationID, int PageSize)
//{
// var noticeCount=GetNoticeListForEditCount( roll, organizationID);
// var pageCount = noticeCount / PageSize;
// if (noticeCount % PageSize != 0)
// {
// pageCount++;
// }
// return pageCount;
//}
public static int GetNoticeListForEditCount(SystemRollEnum roll, string organizationID)
{
using (var utility = DbUtility.GetInstance())
{
var sql = new StringBuilder();
sql.Append(@"
SELECT
Count(`t_notice`.`NoticeID`) as NoticeCount
FROM `t_notice`
where ");
if (roll == SystemRollEnum.SysAdmin)
{
sql.Append(@"
(`t_notice`.`OrganizationID` is null or `t_notice`.`OrganizationID`=@OrganizationID)");
}
else
{
sql.Append(@"
`t_notice`.`OrganizationID`=@OrganizationID");
}
sql.Append(@";");
utility.AddParameter("OrganizationID", organizationID);
var list = utility.ExecuteReader(sql.ToString());
foreach (var dic in list)
{
var noticeCount = Converts.ToTryInt(dic["NoticeCount"], 0);
return(noticeCount);
}
}
return(0);
}
//protected override DataTable BuildSchemaTable()
//{
// return base.BuildSchemaTable(true);
//}
public override IExtents GetExtents()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText =
string.Format(
"SELECT MIN({0}_Envelope_MinX), MIN({0}_Envelope_MinY), MAX({0}_Envelope_MaxX), MAX({0}_Envelope_MaxY) FROM {1}.{2} {3}",
GeometryColumn, TableSchema, Table,
GetWithClause(DefaultProviderProperties == null
? null
: DefaultProviderProperties.ProviderProperties.Collection));
cmd.CommandType = CommandType.Text;
conn.Open();
using (IDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (r.Read())
{
if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3))
{
return(GeometryFactory.CreateExtents());
}
double xmin = r.GetDouble(0);
double ymin = r.GetDouble(1);
double xmax = r.GetDouble(2);
double ymax = r.GetDouble(3);
return(GeometryFactory.CreateExtents2D(xmin, ymin, xmax, ymax));
}
}
return(GeometryFactory.CreateExtents());
}
}
protected override void ReadSpatialReference(out ICoordinateSystem cs, out string srid)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT TOP 1 SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = @pschema and F_TABLE_NAME = @ptablename";
cmd.Parameters.Add(DbUtility.CreateParameter("pschema", TableSchema, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("ptablename", Table, ParameterDirection.Input));
conn.Open();
object result = cmd.ExecuteScalar();
if (result is int)
{
int isrid = (int)result;
cs = SridMap.DefaultInstance.Process(isrid, (ICoordinateSystem)null);
srid = !Equals(cs, default(ICoordinateSystem)) ? SridMap.DefaultInstance.Process(cs, "") : "";
return;
}
}
}
cs = default(ICoordinateSystem);
srid = "";
}
protected virtual IDbCommand PrepareSelectCommand(Expression query)
{
Expression exp = ExpressionMerger.MergeExpressions(query, DefinitionQuery);
//if (DefaultProviderProperties != null)
//{
// var attributes =
// GetProviderPropertyValue<AttributesCollectionExpression, CollectionExpression<PropertyNameExpression>>(
// DefaultProviderProperties.ProviderProperties,
// null);
// if (attributes != null)
// exp = ExpressionMerger.MergeExpressions(exp, new AttributesProjectionExpression(attributes));
//}
ExpressionTreeToSqlCompilerBase <TOid> compiler = CreateSqlCompiler(exp);
List <ProviderPropertyExpression> props =
new List <ProviderPropertyExpression>(
MergeProviderProperties(
compiler.ProviderProperties,
DefaultProviderProperties == null
? null
: DefaultProviderProperties.ProviderProperties.Collection));
IDbCommand cmd = DbUtility.CreateCommand();
cmd.CommandText = GenerateSelectSql(props, compiler);
cmd.CommandType = CommandType.Text;
foreach (IDataParameter p in compiler.ParameterCache.Values)
{
cmd.Parameters.Add(p);
}
return(cmd);
}
public void CreateSridConstraint()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
CreateSridConstraint(conn, TableSchema, Table, GeometryColumn, SridInt.HasValue ? SridInt.Value : 0);
}
}
/// <summary>
/// 取参照实体的最大order值。
/// </summary>
/// <param name="bag"></param>
/// <param name="mode"></param>
/// <param name="offset"></param>
/// <param name="isolation"></param>
/// <returns></returns>
private int GetNewOrderNumber(EntityTreeUpdatingBag bag, EntityTreePosition mode, int offset = 0, Expression <Func <TEntity> > isolation = null)
{
if (bag == null)
{
return(GetNewOrderNumber(isolation));
}
switch (mode)
{
case EntityTreePosition.Children:
var sql = string.Format("SELECT MAX({0}) FROM {1} WHERE {2} LIKE {3}",
GetOrderExpression(),
DbUtility.FormatByQuote(syntax, metadata.TableName),
QuoteColumn(metaTree.InnerSign),
syntax.FormatParameter("pm"));
var innerId = bag.InnerId;
var parameters = new ParameterCollection {
{ "pm", innerId + new string('_', metaTree.SignLength) }
};
return(database.ExecuteScalar((SqlCommand)sql, parameters).To <int>() + 1 + offset);
case EntityTreePosition.Before:
return(bag.Order + offset);
case EntityTreePosition.After:
return(bag.Order + 1 + offset);
}
return(0);
}
/// <summary>
/// 对命令文本进行分段处理，使之能够返回小范围内的数据。
/// </summary>
/// <param name="commandText">命令文本。</param>
/// <param name="segment">数据分段对象。</param>
/// <returns>处理后的分段命令文本。</returns>
/// <exception cref="SegmentNotSupportedException">当前数据库或版本不支持分段时，引发该异常。</exception>
public virtual string Segment(string commandText, IDataSegment segment)
{
var orderBy = DbUtility.FindOrderBy(commandText);
var regAlias = new Regex(@"(\w+)?\.");
//如果有排序
if (!string.IsNullOrEmpty(orderBy))
{
//去除子句中的Order并移到OVER后
commandText = string.Format(@"
SELECT T.* FROM
(
SELECT T.*, ROW_NUMBER() OVER ({2}) AS ROW_NUM
FROM ({0}) T
) T
WHERE {1}",
commandText.Replace(orderBy, string.Empty).Trim(),
segment.Condition("ROW_NUM"),
regAlias.Replace(orderBy, string.Empty));
}
else
{
commandText = string.Format(@"
SELECT T.* FROM
(
SELECT T.*, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROW_NUM
FROM ({0}) T
) T
WHERE {1}",
commandText, segment.Condition("ROW_NUM"));
}
return(commandText);
}
/// <summary>
/// 批量插入集合中的数据。
/// </summary>
/// <param name="database">当前的 <see cref="IDatabase"/> 对象。</param>
/// <param name="collection">要插入的数据的集合。</param>
/// <param name="tableName">表的名称。</param>
/// <param name="mapping">名称和类型的映射字典。</param>
/// <param name="batchSize">每批次写入的数据量。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
private void BatchInsert(IDatabase database, ICollection collection, string tableName, IList <PropertyFieldMapping> mapping, int batchSize, Action <int> completePercentage)
{
//Oracle.DataAccess将每一列的数据构造成一个数组，然后使用参数进行插入
try
{
database.Connection.TryOpen();
using (var command = database.Provider.CreateCommand(database.Connection, database.Transaction, null))
{
var syntax = database.Provider.GetService <ISyntaxProvider>();
var sql = string.Format("INSERT INTO {0}({1}) VALUES({2})",
DbUtility.FormatByQuote(syntax, tableName),
#if NET35
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName)).ToArray()), string.Join(",", mapping.Select(s => syntax.ParameterPrefix + s).ToArray()));
#else
string.Join(",", mapping.Select(s => DbUtility.FormatByQuote(syntax, s.FieldName))), string.Join(",", mapping.Select(s => syntax.ParameterPrefix + s.FieldName)));
#endif
command.CommandText = sql;
var length = Math.Min(batchSize, collection.Count);
var count = collection.Count;
var data = InitArrayData(mapping.Count, length);
SetArrayBindCount(command, length);
BatchSplitData(collection, batchSize,
(index, batch, item) =>
{
if (mapping == null)
{
mapping = GetNameTypeMapping(item);
}
FillArrayData(mapping, item, data, batch);
},
(index, batch, surplus, lastBatch) =>
{
AddOrReplayParameters(syntax, mapping, command.Parameters, data,
() => database.Provider.DbProviderFactory.CreateParameter());
command.ExecuteNonQuery();
if (completePercentage != null)
{
completePercentage((int)(((index + 1.0) / count) * 100));
}
if (!lastBatch)
{
length = Math.Min(batchSize, surplus);
data = InitArrayData(mapping.Count, length);
SetArrayBindCount(command, length);
}
});
}
}
catch (Exception exp)
{
throw new BatcherException(collection, exp);
}
}
public void BatchInsert(IEnumerable <TEntity> entities, int batchSize = 1000, Action <int> completePercentage = null)
{
var batcher = context.Database.Provider.GetService <IBatcherProvider>();
if (batcher == null)
{
throw new EntityPersistentException(SR.GetString(SRKind.NotSupportBatcher), null);
}
var syntax = context.Database.Provider.GetService <ISyntaxProvider>();
var rootType = typeof(TEntity).GetRootType();
var tableName = string.Empty;
entities.ForEach(s => EntityPersistentSubscribePublisher.OnBeforeCreate(s));
//if (Environment != null)
{
// tableName = DbUtility.FormatByQuote(syntax, Environment.GetVariableTableName(rootType));
}
//else
{
var metadata = EntityMetadataUnity.GetEntityMetadata(rootType);
tableName = DbUtility.FormatByQuote(syntax, metadata.TableName);
}
batcher.Insert(context.Database, entities, tableName, batchSize, completePercentage);
}
/// <summary>
/// 将 <see cref="DataTable"/> 的数据批量插入到数据库中。
/// </summary>
/// <param name="database">提供给当前插件的 <see cref="IDatabase"/> 对象。</param>
/// <param name="dataTable">要批量插入的 <see cref="DataTable"/>。</param>
/// <param name="batchSize">每批次写入的数据量。</param>
/// <param name="completePercentage">已完成百分比的通知方法。</param>
public void Insert(IDatabase database, DataTable dataTable, int batchSize = 1000, Action <int> completePercentage = null)
{
if (!BatcherChecker.CheckDataTable(dataTable))
{
return;
}
try
{
database.Connection.TryOpen();
//给表名加上前后导符
var tableName = DbUtility.FormatByQuote(database.Provider.GetService <ISyntaxProvider>(), dataTable.TableName);
using (var bulk = new SqlBulkCopy((SqlConnection)database.Connection, SqlBulkCopyOptions.KeepIdentity, (SqlTransaction)database.Transaction)
{
DestinationTableName = tableName,
BatchSize = batchSize
})
using (var reader = new DataTableBatchReader(bulk, dataTable))
{
bulk.WriteToServer(reader);
}
}
catch (Exception exp)
{
throw new BatcherException(dataTable.Rows, exp);
}
}
public int DeleteUnit(string unitId, string modifyBy)
{
string funMsg = "Function: DeleteUnit(string unitId, string modifyBy)" + FileUtility.NewLine + _ClassMsg;
try
{
string procedure = "spDeleteUnit";
return(DbUtility.ExecuteNonQueryByProc(procedure, this.ConnStr,
new SqlParameter
{
ParameterName = "@unitId",
SqlDbType = SqlDbType.VarChar,
Value = unitId
},
new SqlParameter
{
ParameterName = "@modifyBy",
SqlDbType = SqlDbType.Char,
Value = modifyBy
}
));
}
catch (Exception ex)
{
throw ex;
}
}
public UserModel GetUserModelByLoginName(string organizationId, string loginName)
{
var model = new UserModel();
using (var utility = DbUtility.GetInstance())
{
utility.AddParameter("LoginName", loginName);
utility.AddParameter("OrganizationID", organizationId);
var sql = new StringBuilder();
sql.AppendLine("SELECT ");
sql.AppendLine(" `m_user`.`Guid`, ");
sql.AppendLine(" `m_user`.`OrganizationID`, ");
sql.AppendLine(" `organization`.`OfficeName`, ");
sql.AppendLine(" `organization`.`OfficeNameEn`, ");
sql.AppendLine(" `m_user`.`LoginName`, ");
sql.AppendLine(" `m_user`.`Password`, ");
sql.AppendLine(" `m_user`.`UserName`, ");
sql.AppendLine(" `m_user`.`UserNameKana`, ");
sql.AppendLine(" `m_user`.`UserNameEn`, ");
sql.AppendLine(" `m_user`.`Authority`, ");
sql.AppendLine(" `m_user`.`DisplayNo`, ");
sql.AppendLine(" `m_user`.`DisplayFlag`, ");
sql.AppendLine(" `m_user`.`LastUserID`, ");
sql.AppendLine(" `m_user`.`LastUpdatetime` ");
sql.AppendLine(" FROM m_user ");
sql.AppendLine(" INNER JOIN `m_organization` as `organization` on `organization`.`OrganizationID`=`m_user`.`OrganizationID` ");
sql.AppendLine(" Where LoginName = ?LoginName");
sql.AppendLine(" And m_user.OrganizationID = ?OrganizationID");
utility.ExecuteReaderModel(sql.ToString(), model);
}
return(model);
}
/// <summary>
///
/// </summary>
/// <param name="unitId"></param>
/// <param name="area"></param>
/// <param name="countf"></param>
/// <param name="countt"></param>
/// <param name="countw"></param>
/// <param name="direction"></param>
/// <returns></returns>
public int UpdateUnit(string unitId, decimal area, int countf, int countt, int countw, string direction)
{
string funMsg = "function: UpdateUnit(string item, decimal area, int countf, int countt, int countw, string direction)" + FileUtility.NewLine + _ClassMsg;
string procedureName = string.Empty;
try
{
procedureName = "sp_updunit";//"spUpdateUnit";
return(DbUtility.ExecuteNonQueryByProc(procedureName, this._CentaDbConn, new SqlParameter[] {
new SqlParameter {
ParameterName = "@unitId", SqlDbType = SqlDbType.NVarChar, Value = unitId
},
new SqlParameter {
ParameterName = "@area", SqlDbType = SqlDbType.Decimal, Value = area
},
new SqlParameter {
ParameterName = "@countf", SqlDbType = SqlDbType.Int, Value = countf
},
new SqlParameter {
ParameterName = "@countt", SqlDbType = SqlDbType.Int, Value = countt
},
new SqlParameter {
ParameterName = "@countw", SqlDbType = SqlDbType.Int, Value = countw
},
new SqlParameter {
ParameterName = "@direction", SqlDbType = SqlDbType.NVarChar, Value = direction
},
}));
}
catch (Exception ex)
{
string exMsg = "Exception: " + ex.Message + FileUtility.NewLine + "ProcedureName: " + procedureName + FileUtility.NewLine + funMsg;
throw new Exception(exMsg, ex.InnerException);
}
}
/// <summary>
///
/// </summary>
/// <param name="keyword"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetEstateNameByKeyword(string keyword, string type, string code, int pageIndex, int pageSize, out int recordCount)
{
string funMsg = "function: GetEstateNameByKeyword(string keyword, string type, string code, int pageIndex, int pageSize, out int recordCount)" + FileUtility.NewLine + _ClassMsg;
string procedureName = string.Empty;
try
{
procedureName = "spListEstateNameByKeyword";
return(DbUtility.GetDataTableByProc(procedureName, this._CentaDbConn, out recordCount, new SqlParameter[] {
new SqlParameter {
ParameterName = "@keyword", SqlDbType = SqlDbType.NVarChar, Value = keyword
},
new SqlParameter {
ParameterName = "@type", SqlDbType = SqlDbType.NVarChar, Value = type
},
new SqlParameter {
ParameterName = "@code", SqlDbType = SqlDbType.NVarChar, Value = code
},
new SqlParameter {
ParameterName = "@pageIndex", SqlDbType = SqlDbType.Int, Value = pageIndex
},
new SqlParameter {
ParameterName = "@pageSize", SqlDbType = SqlDbType.Int, Value = pageSize
},
new SqlParameter {
ParameterName = "@recordCount", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output
}
}));
}
catch (Exception ex)
{
string exMsg = "Exception: " + ex.Message + FileUtility.NewLine + "ProcedureName: " + procedureName + FileUtility.NewLine + funMsg;
throw new Exception(exMsg, ex.InnerException);
}
}
/// <summary>
///
/// </summary>
/// <param name="unitId"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
public int MoveUnit(string unitId, string row, string col)
{
string funMsg = "function: MoveUnit(string unitId, string row, string col)" + FileUtility.NewLine + _ClassMsg;
string procedureName = string.Empty;
try
{
procedureName = "sp_moveunit";//"spMoveUnit";
return(DbUtility.ExecuteNonQueryByProc(procedureName, this._CentaDbConn, new SqlParameter[] {
new SqlParameter {
ParameterName = "@unitId", SqlDbType = SqlDbType.NVarChar, Value = unitId
},
new SqlParameter {
ParameterName = "@row", SqlDbType = SqlDbType.NVarChar, Value = row
},
new SqlParameter {
ParameterName = "@col", SqlDbType = SqlDbType.NVarChar, Value = col
}
}));
}
catch (Exception ex)
{
string exMsg = "Exception: " + ex.Message + FileUtility.NewLine + "ProcedureName: " + procedureName + FileUtility.NewLine + funMsg;
throw new Exception(exMsg, ex.InnerException);
}
}
/// <summary>
/// 通过片区获取(分页)楼盘
/// </summary>
/// <param name="scopeId">片区primarykey</param>
/// <param name="pageIndex">当前页</param>
/// <param name="pageSize">每页数量</param>
/// <param name="recordCount">总页数</param>
/// <returns></returns>
public DataTable GetEstateByScope(string scopeId, int pageIndex, int pageSize, out int recordCount)
{
string funMsg = "function: GetEstateByScope(string scopeId)" + FileUtility.NewLine + _ClassMsg;
string procedureName = string.Empty;
try
{
procedureName = "sp_getallest";//"spListEstateByScope";
return(DbUtility.GetDataTableByProc(procedureName, _CentaDbConn, out recordCount, new SqlParameter[] {
new SqlParameter {
ParameterName = "@scopeId", SqlDbType = SqlDbType.Char, Value = scopeId
},
new SqlParameter {
ParameterName = "@pageIndex", SqlDbType = SqlDbType.Int, Value = pageIndex
},
new SqlParameter {
ParameterName = "@pageSize", SqlDbType = SqlDbType.Int, Value = pageSize
},
new SqlParameter {
ParameterName = "@recordCount", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output
}
}));
}
catch (Exception ex)
{
string exMsg = "Exception: " + ex.Message + FileUtility.NewLine + "ProcedureName: " + procedureName + FileUtility.NewLine + funMsg;
throw new Exception(exMsg, ex.InnerException);
}
}
/// <summary>
///
/// </summary>
/// <param name="estate"></param>
/// <returns></returns>
public int UpdateEstate(CentaEstateType estate)
{
string funMsg = "function: UpdateEstate(CentaEstateType estate)" + FileUtility.NewLine + _ClassMsg;
string procedureName = string.Empty;
try
{
procedureName = "sp_updest";//"spUpdateEstate";
return(DbUtility.ExecuteNonQueryByProc(procedureName, this._CentaDbConn, new SqlParameter[] {
new SqlParameter {
ParameterName = "@scopeId", SqlDbType = SqlDbType.NVarChar, Value = estate.ScopeId
},
new SqlParameter {
ParameterName = "@estateId", SqlDbType = SqlDbType.NVarChar, Value = estate.EstateId
},
new SqlParameter {
ParameterName = "@estateName", SqlDbType = SqlDbType.NVarChar, Value = estate.EstateName
},
new SqlParameter {
ParameterName = "@estateType", SqlDbType = SqlDbType.NVarChar, Value = estate.EstateType
},
new SqlParameter {
ParameterName = "@address", SqlDbType = SqlDbType.NVarChar, Value = estate.Address
},
new SqlParameter {
ParameterName = "@phase", SqlDbType = SqlDbType.NVarChar, Value = estate.Phase
}
}));
}
catch (Exception ex)
{
string exMsg = "Exception: " + ex.Message + FileUtility.NewLine + "ProcedureName: " + procedureName + FileUtility.NewLine + funMsg;
throw new Exception(exMsg, ex.InnerException);
}
}
/// <summary>
///
/// </summary>
/// <param name="buildId"></param>
/// <param name="floor"></param>
/// <param name="room"></param>
/// <returns></returns>
public DataTable GetUnitByBuild(string buildId, string floor, string room)
{
string funMsg = "function: GetUnitByBuild(string buildId, string floor, string room)" + FileUtility.NewLine + _ClassMsg;
string procedureName = string.Empty;
try
{
procedureName = "sp_getallunit_DB";//"spListUnitByBuild";
return(DbUtility.GetDataTableByProc(procedureName, _CentaDbConn, new SqlParameter[] {
new SqlParameter {
ParameterName = "@buildId", SqlDbType = SqlDbType.NVarChar, Value = buildId
},
new SqlParameter {
ParameterName = "@floor", SqlDbType = SqlDbType.NVarChar, Value = floor
},
new SqlParameter {
ParameterName = "@room", SqlDbType = SqlDbType.NVarChar, Value = room
}
}));
}
catch (Exception ex)
{
string exMsg = "Exception: " + ex.Message + FileUtility.NewLine + "ProcedureName: " + procedureName + FileUtility.NewLine + funMsg;
throw new Exception(exMsg, ex.InnerException);
}
}
private int Add()
{
DbUtility dbUtility = new DbUtility("Server=.;uid=sa;pwd=landa;database=ORMTest", DbProviderType.SqlServer);
SqlParameter[] parms = new SqlParameter[]{
new SqlParameter("@Name",SqlDbType.VarChar,50),
new SqlParameter("@Sex",SqlDbType.VarChar,10),
new SqlParameter("@ClassId",SqlDbType.VarChar,50),
new SqlParameter("@StudentNo",SqlDbType.VarChar,50)
};
parms[0].Value = "SimpleTest";
parms[1].Value = "男";
parms[2].Value = "09461";
parms[3].Value = "0946101";
int result = dbUtility.ExecuteNonQuery("insert into Student(Name,Sex,ClassId,StudentNo)values(@Name,@Sex,@ClassId,@StudentNo)", parms);
return result;
}
private DbCommand GetDbCommandFromCommandText(DbUtility actualData)
{
DbCommand cmd = actualData.CreateDbCommand();
if (cmd != null)
{
cmd.CommandText = CommandText.Trim();
cmd.CommandTimeout = TimeOut;
cmd.CommandType = (CommandType) Enum.Parse(typeof (CommandType), CommandType.ToString());
MatchCollection paramsName = Regex.Matches(CommandText, REGEX_GET_PARAMS, RegexOptions.IgnoreCase);
MatchCollection localDefParams = Regex.Matches(CommandText, REGEX_GET_LOCAL_PARAMS,
RegexOptions.IgnoreCase);
var tempParams = new List<string>();
var tempLocalParams = new List<string>();
foreach (Match match in paramsName)
{
tempParams.Add(match.Value);
}
foreach (Match match in localDefParams)
{
tempLocalParams.Add(match.Value);
}
tempParams.RemoveAll(tempLocalParams.Contains);
foreach (string match in tempParams)
{
if (!cmd.Parameters.Contains(match))
{
DbParameter param = cmd.CreateParameter();
param.ParameterName = match;
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
cmd.Parameters.Add(param);
}
}
return cmd;
}
return null;
}
private DbCommand GetDbCommand(DbUtility actualData)
{
DbCommand cmd = actualData.CreateDbCommand();
if (cmd != null)
{
cmd.CommandText = CommandText.Trim();
cmd.CommandTimeout = TimeOut;
cmd.CommandType = (CommandType) Enum.Parse(typeof (CommandType), CommandType.ToString());
if (Parameters != null && Parameters.Param != null && Parameters.Param.Length > 0)
{
foreach (DataOperationsDataCommandParametersParam param in Parameters.Param)
{
cmd.Parameters.Add(param.GetDbParameter(cmd));
}
}
return cmd;
}
return null;
}
private void btn_delete_Click(object sender, EventArgs e)
{
DialogResult dr;
string order_sn = null;
dr = MessageBox.Show("是否删除", "标题", MessageBoxButtons.OKCancel);
if (dr == DialogResult.OK)
{
//MessageBox.Show("是");
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
if (dataGridView1.Rows[i].Cells[0].Value != null)
{
order_sn = order_sn + dataGridView1.Rows[i].Cells["order_sn"].Value + ",";
}
}
sql = string.Format("delete from cisdb_yyy.dbo.general_order where order_sn in ({0})",order_sn.Substring(0,order_sn.Length-1));
DbUtility db = new DbUtility(DbProviderType.SqlServer);
if (db.ExecuteNonQuery(sql, null) > 0)
{
MessageBox.Show("删除成功！");
dataGridView1.DataSource = DbHelper.getOrderByPatientId(hd_patientId, tb_admissTimes.Text);
rb_all.Checked = true;
}
}
}
public static List<Student> GetList()
{
DbUtility dbUtility = new DbUtility("Server=.;uid=sa;pwd=landa;database=ORMTest", DbProviderType.SqlServer);
return dbUtility.QueryForList<Student>("select * from Student", null);
}
public GetConnection()
{
DbUtility du = new DbUtility(connectionString, DbProviderType.MySql);
this.dbu = du;
}
