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