public datacontrol(String path) { bool flag; if(File. Exists(path)){ flag=false; }else{ flag=true; } SQLiteConnection con = new SQLiteConnection ("Data Source="+path); str = path; con.Open(); connection=con; cmd=new SQLiteCommand(con); if(flag){ cmd.CommandText="create table driver(driverid varchar PRIMARY KEY,personid varchar not null,sex bool not null,name varchar not null,birthday char(8) not null,password varchar not null)"; cmd.ExecuteNonQuery(); cmd.CommandText="create table adminstrator (id varchar PRIMARY KEY,password varchar not null)"; cmd.ExecuteNonQuery(); cmd.CommandText="insert into adminstrator values(\'adminstrator\',\'1234\')"; cmd.ExecuteNonQuery(); cmd.CommandText="create table car(carid varchar primary key,caryear int,avaliable bool,insurancen bool,yearcheck bool)"; cmd.ExecuteNonQuery(); cmd.CommandText="create table contract(carid varchar,driverid varchar,startdate int,enddate int,isagree bool,isvalid bool,PRIMARY KEY(carid,driverid,startdate))"; cmd.ExecuteNonQuery(); //TODO create the table } }
public void AddFingerprint(FingerprintCard card, Image newprint) { using (var newconnect = new DbConnection()) { if (!newconnect.Connect()) throw new CannotConnectToDatabaseException(); string query; if (card.FingerprintList.Count == 0) //card is empty => register new card { query = "insert into usertbl values(\"" + card.guid + "\",\"" + card.FirstName + "\",\"" + card.LastName + "\");"; newconnect.SendQueryAndClose(query); } query = "insert into fprinttbl values(?File,\"" + card.guid + "\");"; byte[] rawimg = SerializeImage(newprint); MySqlParameter pFile = new MySqlParameter("?File", rawimg); newconnect.SendQueryAndClose(query, pFile); card.FingerprintList.Add(newprint); } CachedDB.UpdateCard(card); }
public async Task<dynamic> Create(ToDoItem item) { try { //Reference the Cloudant db. var myCouch = new DbConnection(); var client = myCouch.GetClient(); // Post/Insert to Cloudant using myCouch var response = await client.Documents.PostAsync("{\"text\":\"" + item.text + "\"}"); if (response.IsSuccess) { return "{\"id\":\"" + response.Id + "\",\"rev\":\"" + response.Rev + "\",\"text\":\"" + item.text + "\"}"; } else { return "{\"msg\": \"Failure to POST. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}"; } } catch (Exception e) { return "{\"msg\": \"Failure to POST: " + e + "\"}"; } }
public static ArrayList retrieveConversation(LoggedUser user, Host host) { ArrayList conversation = new ArrayList(new ArrayList()); DbConnection db = new DbConnection(); SqlConnection connection = db.OpenConnection(); int conversation_id = 0; String query = "SELECT id FROM Conversation WHERE host=@host AND loggedUser=@user"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@host", host.loginName); command.Parameters.AddWithValue("@user", user.loginName); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { conversation_id = reader.GetInt32(0); } db.CloseConnection(); db.OpenConnection(); String query1 = "SELECT id,content,initiator,message_read FROM Message WHERE conversation_id=@id"; SqlCommand command1 = new SqlCommand(query1, connection); command1.Parameters.AddWithValue("@id", conversation_id); SqlDataReader reader1 = command1.ExecuteReader(); while (reader1.Read()) { int id = reader1.GetInt32(0); String message = reader1.GetString(1); String initiator = reader1.GetString(2); int read = reader1.GetInt32(3); ArrayList array = new ArrayList(); array.Add(message); array.Add(initiator); array.Add(read); array.Add(id); conversation.Add(array); } db.CloseConnection(); return conversation; }
public DatabaseDriver() { this.DatabaseEngine = Config.GetDatabaseEngine(); DbConnectionStringBuilder Builder; if (this.DatabaseEngine == DatabaseEngine.Sqlite) { Builder = new SQLiteConnectionStringBuilder(); string FullPath = Path.Combine(Utils.AssemblyPath, Config.GetType<string>("Database", "Database") + ".sqlite3"); IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0; Builder.Add("Data Source", FullPath); Connection = new SQLiteConnection(Builder.ConnectionString); } else if (this.DatabaseEngine == DatabaseEngine.Mysql) { Builder = new MySqlConnectionStringBuilder(); Builder.Add("Server", Config.GetType<string>("Database", "Hostname")); Builder.Add("Port", Config.GetType<int>("Database", "Port")); Builder.Add("User ID", Config.GetType<string>("Database", "Username")); Builder.Add("Password", Config.GetType<string>("Database", "Password")); Builder.Add("Database", Config.GetType<string>("Database", "Database")); Connection = new MySqlConnection(Builder.ConnectionString); } else { throw new Exception("Invalid Database type."); } }
protected void Page_Load(object sender, EventArgs e) { //DbConnection 인스턴스 초기화 DbConnection DbConn = new DbConnection(); SqlConnection conn = DbConn.DbConn(); ////프로시저명 //string SpName = "SP_BoardList"; //SqlCommand cmd = new SqlCommand(SpName, conn); //cmd.CommandType = CommandType.StoredProcedure; ////SqlDataReader 사용 //SqlDataReader objDr = cmd.ExecuteReader(); //this.List.DataSource = objDr; //this.List.DataBind(); //SqlDataAdapter 사용 SqlDataAdapter da = new SqlDataAdapter("SP_BoardList", conn); da.SelectCommand.CommandType = CommandType.StoredProcedure; //DataSet 사용 DataSet ds = new DataSet(); da.Fill(ds, "BoardList"); List.DataSource = ds; List.DataBind(); }
public async Task<string> update(ToDoItem item) { try { var dbRef = new DbConnection(); var client = dbRef.GetClient(); //setup updated fields. var data = "{\"text\":\"" + item.text + "\"}"; //Update var response = await client.Documents.PutAsync(item.id, item.rev, data); if (response.IsSuccess) { return "{\"id\":\"" + response.Id + "\",\"rev\":\"" + response.Rev + "\",\"text\":\"" + item.text + "\"}"; } else { return "{\"msg\": \"Failure to PUT. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}"; } } catch (Exception e) { return "{\"msg\": \"Failure to PUT." + e + "\"}"; } }
public async Task<dynamic> GetAll() { try { var dbRef = new DbConnection(); var client = dbRef.GetClient(); //Create the DB if it does not exist. if (client.Database.GetAsync().Result.Error == "not_found") { await dbRef.CreateDB(); //load with initial data await client.Documents.PostAsync("{\"text\":\"Sample 1\"}"); await client.Documents.PostAsync("{\"text\":\"Sample 2\"}"); await client.Documents.PostAsync("{\"text\":\"Sample 3\"}"); } //Query for all docs including full content of the docs. var query = new QueryViewRequest("_all_docs").Configure(query1 => query1.IncludeDocs(true)); //GET RawResponse response = await client.Views.QueryRawAsync(query); if (response.IsSuccess) { return response.Content; } else { return "{\"msg\": \"Failure to GET. Status Code: " + response.StatusCode + ". Reason: " + response.Reason + "\"}"; } } catch (Exception e) { return "{\"msg\": \"Failure to GET: " + e + "\"}"; } }
/// <summary> /// Constructor /// </summary> /// <param name="Engine">The string name, from the GetDatabaseEngine() method</param> /// <param name="Host">The Database server IP Address</param> /// <param name="Port">The Database server Port Number</param> /// <param name="DatabaseName">The name of the database</param> /// <param name="User">A username, with database privliages</param> /// <param name="Pass">The password to the User</param> public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass) { // Set class variables, and create a new connection builder this.DatabaseEngine = GetDatabaseEngine(Engine); DbConnectionStringBuilder Builder; if (this.DatabaseEngine == DatabaseEngine.Sqlite) { string FullPath = Path.Combine(MainForm.Root, DatabaseName + ".sqlite3"); IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0; Builder = new SQLiteConnectionStringBuilder(); Builder.Add("Data Source", FullPath); Connection = new SQLiteConnection(Builder.ConnectionString); } else if (this.DatabaseEngine == DatabaseEngine.Mysql) { IsNewDatabase = false; Builder = new MySqlConnectionStringBuilder(); Builder.Add("Server", Host); Builder.Add("Port", Port); Builder.Add("User ID", User); Builder.Add("Password", Pass); Builder.Add("Database", DatabaseName); Connection = new MySqlConnection(Builder.ConnectionString); } else { throw new Exception("Invalid Database type."); } }
/// <summary> /// 构造函数 /// </summary> public DBHelper(string providerName, string connectionString, string sqlType) { this._sqlType = sqlType; if (string.Compare(sqlType, "Mysql", true) == 0) { _connection = new MySqlConnection(); _command = new MySqlCommand(); this._paraPrefix = "?"; } else { _provider = GetDbProvider(providerName); _connection = _provider.CreateConnection(); _command = _provider.CreateCommand(); if (string.Compare(sqlType, "Oracle", true) == 0) { this._paraPrefix = ":"; } else if (string.Compare(sqlType, "SQLServer", true) == 0) { this._paraPrefix = "@"; } } _connection.ConnectionString = connectionString; _command.Connection = _connection; }
private void BindGrid(DbConnection conn) { conn.Open(); _schema = new DataSet(); var schema = conn.GetSchema(); foreach (DataRow dataRow in schema.Rows) { var tableName = dataRow["CollectionName"].ToString(); if(!_schema.Tables.Contains(tableName)) { var dt = conn.GetSchema(tableName); dt.TableName = tableName; _schema.Tables.Add(dt); } } conn.Close(); dgSchema.DataSource = _schema.Tables[0]; cbTable.DataSource = _schema.Tables[0]; cbTable.DisplayMember = "CollectionName"; cbTable.ValueMember = "CollectionName"; _previousWidth = dgSchema.Width; _previousState = WindowState; }
public static DbParameter[] DeriveParameters(DbConnection dbConnection, string spName) { if (dbConnection == null) { throw new ArgumentNullException("dbConn"); } if (string.IsNullOrEmpty(spName)) { throw new ArgumentNullException("spName"); } DbCommand command = null; using (DbConnection connection = (DbConnection) ((ICloneable) dbConnection).Clone()) { command = connection.CreateCommand(); command.CommandText = spName; command.CommandType = CommandType.StoredProcedure; connection.Open(); if (!(command is SqlCommand)) { throw new NotSupportedException(); } SqlCommandBuilder.DeriveParameters((SqlCommand) command); connection.Close(); } if ((command.Parameters.Count > 0) && (command.Parameters[0].Direction == ParameterDirection.ReturnValue)) { command.Parameters.RemoveAt(0); } DbParameter[] array = new DbParameter[command.Parameters.Count]; command.Parameters.CopyTo(array, 0); return array; }
protected DBAccessBase() { SetConfiguration(); this.dbConnection = dbFactory.CreateConnection(); this.dbConnection.ConnectionString = strConnectionString; this.dbTransaction = null; }
private DbCommand CreateCommand(string commandText, DbConnection connection) { DbCommand command = factory.CreateCommand(); command.Connection = connection; command.CommandText = commandText; return command; }
public FingerprintCard[] GetFingerprints() { FingerprintCard[] resultarray = CachedDB.getCacheDb(); //getCache if (resultarray != null) return resultarray; using (var newconnect = new DbConnection()) { if (!newconnect.Connect()) throw new CannotConnectToDatabaseException(); string query = "select * from usertbl;"; MySqlDataReader reader = newconnect.SendQuery(query); List<FingerprintCard> fingerprintlist = new List<FingerprintCard>(); while (reader.Read()) { FingerprintCard newcard = new FingerprintCard(reader.GetValue(1).ToString(), reader.GetValue(2).ToString()); string guidstr = (string)reader.GetValue(0); newcard.guid = new Guid(guidstr); newcard.FingerprintList = GetFingerprintList(newcard.guid); fingerprintlist.Add(newcard); } reader.Close(); resultarray = fingerprintlist.ToArray(); } CachedDB.UpdateCache(resultarray); return resultarray; }
public byte[] GetBytes(DbConnection connection) { using (var contentStream = GetContentStream(connection)) { return contentStream.Length == 0 ? null : new BinaryReader(contentStream).ReadBytes((int)contentStream.Length); } }
public Boolean saveImageInDb() { DbConnection db = new DbConnection(); SqlConnection connection = db.OpenConnection(); String query = "INSERT INTO Images (name, contentType, property, userLogin, data) VALUES (@name, @contentType, @property, @user, @data);"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@contentType", contentType); if (propertyId != -1) { command.Parameters.AddWithValue("@property", propertyId); } else { command.Parameters.AddWithValue("@property", "NULL"); } command.Parameters.AddWithValue("@user", user); command.Parameters.AddWithValue("@data", bytes); try { System.Diagnostics.Debug.Write(command.CommandText); command.ExecuteNonQuery(); db.CloseConnection(); return true; } catch (Exception ex) { System.Diagnostics.Debug.Write(ex.Message); db.CloseConnection(); return false; } }
public override void DoJob() { StringBuilder sb = new StringBuilder(); // Result of the query. sb.AppendFormat("JobType: {0}", GetName()).AppendLine(); /* * TODO: Properly read in connection information here. */ DbConnectorInfo sourceInfo = new DbConnectorInfo("localhost\\SQLEXPRESS", "NORTHWND", "sa", "Conestoga1"); DbConnection sourceConnection = new DbConnection(sourceInfo, "MS"); Dictionary<string, string> keys = new Dictionary<string, string>(); keys.Add("Customers", "CustomerID"); keys.Add("Orders", "OrderID"); List<string> joins = new List<string>(); joins.Add("full"); sb.Append("Connecting to source...").AppendLine(); sb.AppendFormat("DbConnectorInfo:").AppendLine(); sb.AppendFormat("server: {0}", sourceInfo.server).AppendLine(); sb.AppendFormat("database: {0}", sourceInfo.database).AppendLine(); sb.AppendFormat("userid: {0}", sourceInfo.userid).AppendLine(); /* * Open the source and destination databases. */ if (!sourceConnection.ConnectionOpen) { sourceConnection.OpenDBConnection(); } /* * Pull data from BioLinks. * TODO: Figure out wtf this method even takes. */ DataTable sourceData = sourceConnection.pullData(keys, joins, null, null); /* * Log the query. */ foreach (DataRow row in sourceData.Rows) { foreach (DataColumn col in sourceData.Columns) { sb.AppendFormat("{0} ", row[col]); } sb.AppendLine(); } Logger.logMessage(sb.ToString(), AppDomain.CurrentDomain.BaseDirectory + AppDomain.CurrentDomain.RelativeSearchPath + "\\Events2.txt"); /* * Close the source and destination databases. */ sourceConnection.CloseDBConnection(); }
public static void DeleteQueuesForEndpoint(DbConnection connection, string schema, string endpointName) { DeleteQueue(connection, schema, endpointName); DeleteQueue(connection, schema, $"{endpointName}.{Environment.MachineName}"); DeleteQueue(connection, schema, $"{endpointName}.Retries"); DeleteQueue(connection, schema, $"{endpointName}.Timeouts"); DeleteQueue(connection, schema, $"{endpointName}.TimeoutsDispatcher"); }
public static void EndConn() { if (conn != null) { conn.Close(); conn = null; } }
public StorageSession(DbConnection connection, DbTransaction transaction, bool ownsTransaction) { Guard.AgainstNull(nameof(connection), connection); Guard.AgainstNull(nameof(transaction), transaction); Connection = connection; this.ownsTransaction = ownsTransaction; Transaction = transaction; }
//connects to SQL Server here public DAL() { conn = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ProviderName).CreateConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString; cmd = conn.CreateCommand(); cmd.Connection = conn; conn.Open(); cmd.CommandType = CommandType.Text; }
public void OnBeforeEachTest() { DbConnection.Init("ConnectionString"); using (var db = new DbConnection()) { db.DropCreateTableAsync<Person>().Wait(); } }
public static DbConnection Instance() { if (_connection == null) { _connection = new DbConnection(); } return _connection; }
protected override string GetDatabaseName(DbConnection dbConnection) { var name = base.GetDatabaseName(dbConnection); if (name.IsNullOrEmpty()) name = Path.GetFileNameWithoutExtension(GetDataSourceName(dbConnection)); return name; }
public static void Initialize(Config config) { conn = new MySqlConnection( String.Format("Server={0};Port={1};Database={2};Uid={3};Pwd={4};", config.DatabaseHost, config.DatabasePort, config.DatabaseName, config.DatabaseUser, config.DatabasePassword)); }
async Task Unsubscribe(Subscriber subscriber, DbConnection connection, MessageType messageType) { using (var command = connection.CreateCommand()) { command.CommandText = subscriptionCommands.Unsubscribe; command.AddParameter("MessageType", messageType.TypeName); command.AddParameter("Subscriber", subscriber.TransportAddress); await command.ExecuteNonQueryEx(); } }
protected void getView() { //DbConnection 인스턴스 초기화 DbConnection DbConn = new DbConnection(); SqlConnection conn = DbConn.DbConn(); //프로시저명 string SpName = "SP_BoardView"; SqlCommand cmd = new SqlCommand(SpName, conn); cmd.CommandType = CommandType.StoredProcedure; //파라미터 설정 cmd.Parameters.Add("@PK_BoardList", SqlDbType.Int); cmd.Parameters["@PK_BoardList"].Value = Convert.ToInt32(Request.QueryString["ID"].ToString()); SqlDataReader objDr = cmd.ExecuteReader(); while(objDr.Read()){ lblKorName.Text = objDr[1].ToString(); lblEngName.Text = objDr[2].ToString(); lblJumin1.Text = objDr[3].ToString().Substring(0, 6); lblJumin2.Text = objDr[3].ToString().Substring(6); string BirthdayStr = objDr[4].ToString(); lblBirthday.Text = BirthdayStr.Substring(0, 4) + "년 " + BirthdayStr.Substring(4, 2) + "월 " + BirthdayStr.Substring(6) + "일"; if (objDr[5].ToString().Equals(1)) { chkLunar.Checked = true; } else { chkLunar.Checked = false; } lblRepNo.Text = objDr[6].ToString().Substring(0, 3); lblTelMiddleNo.Text = objDr[6].ToString().Substring(3, 4); lblTelLastNo.Text = objDr[6].ToString().Substring(7); string sexType = objDr[7].ToString(); if (objDr[7].ToString().Equals("M")) { lblSex.Text = sexType.Replace(objDr[7].ToString(), "남"); } else { lblSex.Text = sexType.Replace(objDr[7].ToString(), "여"); } lblReligion.Text = objDr[8].ToString(); lblMilitaryType.Text = objDr[9].ToString(); txtRemark.Text = objDr[10].ToString(); } }
public async Task ShouldInsertAnObject() { using (var db = new DbConnection()) { Enumerable.Range(0, 100).ToList().ForEach(_ => db.InsertAsync(new Person {Name = "Rick Bobby"}).Wait()); var people = await db.Select<Person>().SelectAsync(); people.Count().Should().Be(100); } }
public async Task ShouldDropCreateATable() { using (var db = new DbConnection()) { await db.DropTableAsync<Table>(); await db.CreateTableAsync<Table>(); await db.DropCreateTableAsync<Table>(); } }
public static void Configure(DbContextOptionsBuilder <LifeDbContext> builder, DbConnection connection) { builder.UseSqlServer(connection); }
public DbDataReader ExecuteReader(DbCommand command, DbConnection connection) { OnExecuteCommand(command); command.Connection = connection; return command.ExecuteReader(); }
protected static FR_L5AC_GDSfDID_1214_Array Execute(DbConnection Connection, DbTransaction Transaction, P_L5AC_GDSfDID_1214 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { var returnStatus = new FR_L5AC_GDSfDID_1214_Array(); DbCommand command = Connection.CreateCommand(); command.Connection = Connection; command.Transaction = Transaction; var commandLocation = "CL5_MyHealthClub_Actions.Atomic.Retrieval.SQL.cls_Get_DiagnosesStatistics_for_DoctorID.sql"; command.CommandText = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(commandLocation)).ReadToEnd(); CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ticket", securityTicket); command.CommandTimeout = QueryTimeout; command.CommandText = System.Text.RegularExpressions.Regex.Replace(command.CommandText, "=[ \t]*@ListDoctorID", " IN $$ListDoctorID$$"); CSV2Core_MySQL.Support.DBSQLSupport.AppendINStatement(command, "$ListDoctorID$", Parameter.ListDoctorID); List <L5AC_GDSfDID_1214> results = new List <L5AC_GDSfDID_1214>(); var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction); var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader()); try { reader.SetOrdinals(new string[] { "PotentialDiagnosis_Name_DictID", "IfPerfomed_DateOfAction", "Office_Name_DictID", "HEC_DIA_PotentialDiagnosisID", "CMN_STR_OfficeID", "HEC_Doctor_RefID", "HEC_ACT_PerformedActionID", "IsDiagnosisNegated", "DoctorTitle", "DoctorFirstName", "DoctorLastName", "ICD10_Code" }); while (reader.Read()) { L5AC_GDSfDID_1214 resultItem = new L5AC_GDSfDID_1214(); //0:Parameter PotentialDiagnosis_Name of type Dict resultItem.PotentialDiagnosis_Name = reader.GetDictionary(0); resultItem.PotentialDiagnosis_Name.SourceTable = "hec_dia_potentialdiagnoses"; loader.Append(resultItem.PotentialDiagnosis_Name); //1:Parameter IfPerfomed_DateOfAction of type DateTime resultItem.IfPerfomed_DateOfAction = reader.GetDate(1); //2:Parameter Office_Name of type Dict resultItem.Office_Name = reader.GetDictionary(2); resultItem.Office_Name.SourceTable = "cmn_str_offices"; loader.Append(resultItem.Office_Name); //3:Parameter HEC_DIA_PotentialDiagnosisID of type Guid resultItem.HEC_DIA_PotentialDiagnosisID = reader.GetGuid(3); //4:Parameter CMN_STR_OfficeID of type Guid resultItem.CMN_STR_OfficeID = reader.GetGuid(4); //5:Parameter HEC_Doctor_RefID of type Guid resultItem.HEC_Doctor_RefID = reader.GetGuid(5); //6:Parameter HEC_ACT_PerformedActionID of type Guid resultItem.HEC_ACT_PerformedActionID = reader.GetGuid(6); //7:Parameter IsDiagnosisNegated of type bool resultItem.IsDiagnosisNegated = reader.GetBoolean(7); //8:Parameter DoctorTitle of type String resultItem.DoctorTitle = reader.GetString(8); //9:Parameter DoctorFirstName of type String resultItem.DoctorFirstName = reader.GetString(9); //10:Parameter DoctorLastName of type String resultItem.DoctorLastName = reader.GetString(10); //11:Parameter ICD10_Code of type String resultItem.ICD10_Code = reader.GetString(11); results.Add(resultItem); } } catch (Exception ex) { reader.Close(); throw new Exception("Exception occured durng data retrieval in method cls_Get_DiagnosesStatistics_for_DoctorID", ex); } reader.Close(); //Load all the dictionaries from the datatables loader.Load(); returnStatus.Result = results.ToArray(); return(returnStatus); }
/// <summary> /// Throws an <see cref="InvalidOperationException"/> if this method is called /// because the user is responsible for closing <see cref="DbConnection"/>s. /// </summary> /// <param name="conn">The <see cref="DbConnection"/> to clean up.</param> /// <exception cref="InvalidOperationException"> /// Thrown when this method is called. User is responsible for closing /// <see cref="DbConnection"/>s. /// </exception> public override void CloseConnection(DbConnection conn) { throw new InvalidOperationException("The User is responsible for closing ADO.NET connection - not NHibernate."); }
public DataTable ExecuteDataTable(DbCommand command, DbConnection connection) { return ExecuteDataTable(command, 0, 0, connection); }
/// <summary> /// You have to provider a tracking table builder implementation for your current database /// </summary> public abstract IDbBuilderTrackingTableHelper CreateTrackingTableBuilder(DbConnection connection, DbTransaction transaction = null);
/// <summary> /// Perform collection task specific processing. /// </summary> /// /// <param name="taskId">Database assigned task Id.</param> /// <param name="cleId">Database Id of owning Collection Engine.</param> /// <param name="elementId">Database Id of element being collected.</param> /// <param name="databaseTimestamp">Database relatvie task dispatch timestamp.</param> /// <param name="localTimestamp">Local task dispatch timestamp.</param> /// <param name="attributes">Map of attribute names to Id for attributes being collected.</param> /// <param name="scriptParameters">Collection script specific parameters (name/value pairs).</param> /// <param name="connection">Connection script results (null if this script does not /// require a remote host connection).</param> /// <param name="tftpDispatcher">Dispatcher for TFTP transfer requests.</param> /// /// <returns>Collection results.</returns> /// public CollectionScriptResults ExecuteTask( long taskId, long cleId, long elementId, long databaseTimestamp, long localTimestamp, IDictionary <string, string> attributes, IDictionary <string, string> scriptParameters, IDictionary <string, object> connection, string tftpPath, string tftpPath_login, string tftpPath_password, ITftpDispatcher tftpDispatcher) { m_taskId = taskId.ToString(); m_cleId = cleId; m_elementId = elementId; m_databaseTimestamp = databaseTimestamp; m_localTimestamp = localTimestamp; m_attributes = attributes; m_scriptParameters = scriptParameters; m_executionTimer = Stopwatch.StartNew(); ResultCodes resultCode = ResultCodes.RC_BAD_COLLECTION_SCRIPT; DbConnection dbConnection = null; CollectionScriptResults result = null; Lib.Logger.TraceEvent(TraceEventType.Start, 0, "Task Id {0}: Collection script WDMOnMSSQLDeviceDetailsStaticScript.", m_taskId); try { if (connection == null) { resultCode = ResultCodes.RC_NULL_CONNECTION_OBJECT; } else { //Check connection attributes if (connection.ContainsKey("dbConnection")) { dbConnection = connection[@"dbConnection"] as DbConnection; } else { resultCode = ResultCodes.RC_SCRIPT_PARAMETER_MISSING; Lib.Logger.TraceEvent(TraceEventType.Error, 0, "Task Id {0}: Missing dbConnection script parameter.", m_taskId); } } if (dbConnection == null) { resultCode = ResultCodes.RC_NULL_CONNECTION_OBJECT; } else if (dbConnection.State != ConnectionState.Open) { resultCode = ResultCodes.RC_HOST_CONNECT_FAILED; } else { using (dbConnection) { resultCode = GetDeviceDetails(dbConnection); } } // Return RC_SUCCESS even if the query failed, as the database // may be inaccessible resultCode = ResultCodes.RC_SUCCESS; StringBuilder dataRow = new StringBuilder(); dataRow.Append(elementId) .Append(',') .Append(m_attributes[@"deviceDetails"]) .Append(',') .Append(m_scriptParameters[@"CollectorId"]) .Append(',') .Append(m_taskId) .Append(',') .Append(m_databaseTimestamp + m_executionTimer.ElapsedMilliseconds) .Append(',') .Append(@"deviceDetails") .Append(',') .Append(BdnaDelimiters.BEGIN_TAG) .Append(m_deviceDetails.ToString()) .Append(BdnaDelimiters.END_TAG); result = new CollectionScriptResults(resultCode, 0, null, null, null, false, dataRow.ToString()); } catch (Exception e) { Lib.Logger.TraceEvent(TraceEventType.Error, 0, "Task Id {0}: Unhandled exception in WDMOnMSSQLDeviceDetailsStaticScript.\n{1}", m_taskId, e.ToString()); result = new CollectionScriptResults (ResultCodes.RC_PROCESS_EXEC_FAILED, 0, null, null, null, false, null); } Debug.Assert(null != result); Lib.Logger.TraceEvent(TraceEventType.Stop, 0, "Task Id {0}: Connection script WDMOnMSSQLDeviceDetailsStaticScript. Elapsed time {1}. Result code {2}.", m_taskId, m_executionTimer.Elapsed.ToString(), result.ResultCode.ToString()); return(result); }
/// <summary> /// Generate the creating script string (admin only) /// </summary> public string ScriptTable(DbConnection connection, DbTransaction transaction = null) { string str = null; var alreadyOpened = connection.State != ConnectionState.Closed; bool needToCreateTrackingTable = false; try { if (!alreadyOpened) { connection.Open(); } StringBuilder stringBuilder = new StringBuilder(); var tableBuilder = CreateTableBuilder(connection, transaction); // Check if we have mutables columns var hasMutableColumns = TableDescription.MutableColumnsAndNotAutoInc.Any(); // Check if we need to create the tables if (tableBuilder.NeedToCreateTable()) { if (tableBuilder.NeedToCreateSchema()) { stringBuilder.Append(tableBuilder.CreateSchemaScriptText()); } stringBuilder.Append(tableBuilder.CreateTableScriptText()); stringBuilder.Append(tableBuilder.CreatePrimaryKeyScriptText()); } var trackingTableBuilder = CreateTrackingTableBuilder(connection, transaction); trackingTableBuilder.Filters = this.FilterColumns.GetColumnFilters(); if (trackingTableBuilder.NeedToCreateTrackingTable()) { stringBuilder.Append(trackingTableBuilder.CreateTableScriptText()); stringBuilder.Append(trackingTableBuilder.CreatePkScriptText()); stringBuilder.Append(trackingTableBuilder.CreateIndexScriptText()); needToCreateTrackingTable = true; } var triggerBuilder = CreateTriggerBuilder(connection, transaction); triggerBuilder.Filters = this.FilterColumns.GetColumnFilters(); if (triggerBuilder.NeedToCreateTrigger(DbTriggerType.Insert)) { stringBuilder.Append(triggerBuilder.CreateInsertTriggerScriptText()); } if (triggerBuilder.NeedToCreateTrigger(DbTriggerType.Update)) { stringBuilder.Append(triggerBuilder.CreateUpdateTriggerScriptText()); } if (triggerBuilder.NeedToCreateTrigger(DbTriggerType.Delete)) { stringBuilder.Append(triggerBuilder.CreateDeleteTriggerScriptText()); } var procBuilder = CreateProcBuilder(connection, transaction); if (procBuilder != null) { procBuilder.Filters = this.FilterColumns; if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectChanges)) { stringBuilder.Append(procBuilder.CreateSelectIncrementalChangesScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectRow)) { stringBuilder.Append(procBuilder.CreateSelectRowScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertRow)) { stringBuilder.Append(procBuilder.CreateInsertScriptText()); } if (hasMutableColumns && procBuilder.NeedToCreateProcedure(DbCommandType.UpdateRow)) { stringBuilder.Append(procBuilder.CreateUpdateScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteRow)) { stringBuilder.Append(procBuilder.CreateDeleteScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertMetadata)) { stringBuilder.Append(procBuilder.CreateInsertMetadataScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.UpdateMetadata)) { stringBuilder.Append(procBuilder.CreateUpdateMetadataScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteMetadata)) { stringBuilder.Append(procBuilder.CreateDeleteMetadataScriptText()); } if (procBuilder.NeedToCreateProcedure(DbCommandType.Reset)) { stringBuilder.Append(procBuilder.CreateResetScriptText()); } if (this.UseBulkProcedures && procBuilder.NeedToCreateType(DbCommandType.BulkTableType)) { stringBuilder.Append(procBuilder.CreateTVPTypeScriptText()); stringBuilder.Append(procBuilder.CreateBulkInsertScriptText()); if (hasMutableColumns) { stringBuilder.Append(procBuilder.CreateBulkUpdateScriptText()); } stringBuilder.Append(procBuilder.CreateBulkDeleteScriptText()); } if (needToCreateTrackingTable) { stringBuilder.Append(trackingTableBuilder.CreatePopulateFromBaseTableScriptText()); } } str = stringBuilder.ToString(); } finally { if (!alreadyOpened) { connection.Close(); } } return(str); }
/// <summary> /// Gets the table Sync Adapter in charge of executing all command during sync /// </summary> public abstract DbSyncAdapter CreateSyncAdapter(DbConnection connection, DbTransaction transaction = null);
public async Task OpenAsync() { connection = new SqlConnection(registry.ConnectionString); await connection.OpenWithRetryAsync(); }
public DataTable GetSchema(string collection, string[] restrictionValues) { InitDB(); return(DbConnection.GetSchema(collection, restrictionValues)); }
//This constructor is used in tests public SpartanDbContext(DbConnection connection) : base(connection, true) { }
public GetEnquireModel GetEnquire(string chfid) { GetEnquireModel response = new GetEnquireModel(); List <DetailModel> details = new List <DetailModel>(); try { using (var imisContext = new ImisDB()) { var chfidParameter = new SqlParameter("@CHFID", chfid) { SqlDbType = SqlDbType.VarChar, Size = 12 }; var sql = "exec uspAPIGetCoverage @CHFID"; DbConnection connection = imisContext.Database.GetDbConnection(); using (DbCommand cmd = connection.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(new[] { chfidParameter }); if (connection.State.Equals(ConnectionState.Closed)) { connection.Open(); } using (var reader = cmd.ExecuteReader()) { do { bool firstValue = true; while (reader.Read()) { if (firstValue) { response.InsureeName = String.Join(' ', reader["OtherNames"].ToString()) + ' ' + reader["LastName"].ToString(); response.CHFID = reader["CHFID"].ToString(); response.PhotoPath = reader["PhotoPath"].ToString(); response.DOB = reader["DOB"].ToString(); response.Gender = reader["Gender"].ToString(); firstValue = false; } details.Add(new DetailModel { AntenatalAmountLeft = reader["AntenatalAmountLeft"].ToString().ToNullableDecimal(), ConsultationAmountLeft = reader["ConsultationAmountLeft"].ToString().ToNullableDecimal(), DeliveryAmountLeft = reader["DeliveryAmountLeft"].ToString().ToNullableDecimal(), HospitalizationAmountLeft = reader["HospitalizationAmountLeft"].ToString().ToNullableDecimal(), SurgeryAmountLeft = reader["SurgeryAmountLeft"].ToString().ToNullableDecimal(), TotalAdmissionsLeft = reader["TotalAdmissionsLeft"].ToString().ToNullableDecimal(), TotalAntenatalLeft = reader["TotalAntenatalLeft"].ToString().ToNullableDecimal(), TotalConsultationsLeft = reader["TotalConsultationsLeft"].ToString().ToNullableDecimal(), TotalDelivieriesLeft = reader["TotalDelivieriesLeft"].ToString().ToNullableDecimal(), TotalSurgeriesLeft = reader["TotalSurgeriesLeft"].ToString().ToNullableDecimal(), TotalVisitsLeft = reader["TotalVisitsLeft"].ToString().ToNullableDecimal(), PolicyValue = reader["PolicyValue"].ToString().ToNullableDecimal(), EffectiveDate = reader["EffectiveDate"].ToString(), ProductCode = reader["ProductCode"].ToString(), ProductName = reader["ProductName"].ToString(), ExpiryDate = reader["ExpiryDate"].ToString(), Status = reader["Status"].ToString(), DedType = reader["DedType"].ToString().ToNullableFloat(), Ded1 = reader["Ded1"].ToString().ToNullableDecimal(), Ded2 = reader["Ded2"].ToString().ToNullableDecimal(), Ceiling1 = reader["Ceiling1"].ToString().ToNullableDecimal(), Ceiling2 = reader["Ceiling2"].ToString().ToNullableDecimal(), }); } } while (reader.NextResult()); } } } response.Details = details; return(response); } catch (SqlException e) { throw e; } catch (Exception e) { throw e; } }
/// <summary> /// You have to provide a trigger builder implementation for your current database /// </summary> public abstract IDbBuilderTriggerHelper CreateTriggerBuilder(DbConnection connection, DbTransaction transaction = null);
private DbDataReader GetDataReader(DbConnection conn, string strSQL) { DbCommand command = GetCommand(conn, strSQL); return(command.ExecuteReader()); }
/// <summary> /// You have to provide a proc builder implementation for your current database /// </summary> public abstract IDbBuilderProcedureHelper CreateProcBuilder(DbConnection connection, DbTransaction transaction = null);
private ResultCodes GetDeviceDetails(DbConnection dbConnection) { ResultCodes resultCode = ResultCodes.RC_PROCESSING_EXCEPTION; Stopwatch sw = new Stopwatch(); int recordCount = 0; try { Lib.Logger.TraceEvent(TraceEventType.Verbose, 0, "Task Id {0}: Executing device details query.", m_taskId); sw.Start(); m_databaseName = m_scriptParameters["databaseName"]; Lib.Logger.TraceEvent(TraceEventType.Information, 0, "Task Id {0}: Connecting to database {1}.", m_taskId, m_databaseName); dbConnection.ChangeDatabase(m_databaseName); DbCommand cmd = dbConnection.CreateCommand(); cmd.CommandText = @"select client.ClientID, client.Name, network.IP, network.MAC, " + @"platform.Description as Platform, os.Description as OS, " + @"client.Image, hardware.CPU, hardware.CPUSpeed, " + @"hardware.RAM, storage.TotalSpace, hardware.Serial, " + @"hardware.BIOS, hardware.Manufactured " + @"from Client client, ClientHardware hardware, " + @"ClientNetwork network, ClientStorage storage, " + @"Platform platform, OSType os " + @"where client.ClientID = hardware.ClientID " + @"and client.ClientID = network.ClientID " + @"and client.ClientID = storage.ClientID " + @"and hardware.PlatformID = platform.PlatformID " + @"and client.OSTypeID = os.OSTypeID "; cmd.CommandType = CommandType.Text; DbDataReader reader = cmd.ExecuteReader(); Debug.Assert(null != reader); using (reader) { for (recordCount = 0; reader.Read(); ++recordCount) { if (m_deviceDetails.Length > 0) { m_deviceDetails.Append(BdnaDelimiters.DELIMITER_TAG); } m_deviceDetails.Append("ClientID=").Append(reader[@"ClientID"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("Name=").Append(reader[@"Name"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("IPAddress=").Append(reader[@"IP"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("MACAddress=").Append(reader[@"MAC"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("Platform=").Append(reader[@"Platform"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("OS=").Append(reader[@"OS"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("Image=").Append(reader[@"Image"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("CPU=").Append(reader[@"CPU"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("CPUSpeed=").Append(reader[@"CPUSpeed"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("RAM=").Append(reader[@"RAM"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("MediaSize=").Append(reader[@"TotalSpace"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("SerialNumber=").Append(reader[@"Serial"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("BIOS=").Append(reader[@"BIOS"].ToString()).Append(BdnaDelimiters.DELIMITER1_TAG); m_deviceDetails.Append("ManufacturedOn=").Append(reader[@"Manufactured"].ToString()); } Lib.Logger.TraceEvent(TraceEventType.Verbose, 0, "Task Id {0}: Processing device details complete.", m_taskId); } resultCode = ResultCodes.RC_SUCCESS; } catch (DbException dbEx) { Lib.Logger.TraceEvent(TraceEventType.Error, 0, "Task Id {0}: Device detail query failed. Elapsed time {1}.\n{2}", m_taskId, sw.Elapsed.ToString(), FormatDbException(dbEx)); } catch (Exception ex) { Lib.Logger.TraceEvent(TraceEventType.Error, 0, "Task Id {0}: Device detail query failed. Elapsed time {1}.\nRecords processed {2}\n{3}", m_taskId, sw.Elapsed.ToString(), recordCount.ToString(), ex.ToString()); } return(resultCode); }
public void DropProcedures(DbConnection connection, DbTransaction transaction = null) { var alreadyOpened = connection.State != ConnectionState.Closed; // Check if we have mutables columns var hasMutableColumns = TableDescription.MutableColumnsAndNotAutoInc.Any(); try { if (!alreadyOpened) { connection.Open(); } var procBuilder = CreateProcBuilder(connection, transaction); // Could be null if (procBuilder == null) { return; } procBuilder.Filters = this.FilterColumns; if (!procBuilder.NeedToCreateProcedure(DbCommandType.SelectChanges)) { procBuilder.DropSelectIncrementalChanges(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.SelectRow)) { procBuilder.DropSelectRow(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.InsertRow)) { procBuilder.DropInsert(); } if (hasMutableColumns && !procBuilder.NeedToCreateProcedure(DbCommandType.UpdateRow)) { procBuilder.DropUpdate(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.DeleteRow)) { procBuilder.DropDelete(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.InsertMetadata)) { procBuilder.DropInsertMetadata(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.UpdateMetadata)) { procBuilder.DropUpdateMetadata(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.DeleteMetadata)) { procBuilder.DropDeleteMetadata(); } if (!procBuilder.NeedToCreateProcedure(DbCommandType.Reset)) { procBuilder.DropReset(); } if (this.UseBulkProcedures && !procBuilder.NeedToCreateType(DbCommandType.BulkTableType)) { procBuilder.DropBulkInsert(); if (hasMutableColumns) { procBuilder.DropBulkUpdate(); } procBuilder.DropBulkDelete(); procBuilder.DropTVPType(); } } finally { if (!alreadyOpened) { connection.Close(); } } }
public DbConnection CreateConnection() { DbConnection connection = Factory.CreateConnection(); connection.ConnectionString = ConnectionString; return connection; }
public void CreateStoredProcedures(DbConnection connection, DbTransaction transaction = null) { if (!TableDescription.PrimaryKey.HasValue) { throw new InvalidOperationException($"Create stored procedures: Table {TableDescription.TableName} must have at least one dmColumn as Primary key"); } // Check if we have mutables columns var hasMutableColumns = TableDescription.MutableColumnsAndNotAutoInc.Any(); var alreadyOpened = connection.State != ConnectionState.Closed; try { // could be null var procBuilder = CreateProcBuilder(connection, transaction); if (procBuilder == null) { return; } procBuilder.Filters = this.FilterColumns; if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectChanges)) { procBuilder.CreateSelectIncrementalChanges(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.SelectRow)) { procBuilder.CreateSelectRow(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertRow)) { procBuilder.CreateInsert(); } if (hasMutableColumns && procBuilder.NeedToCreateProcedure(DbCommandType.UpdateRow)) { procBuilder.CreateUpdate(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteRow)) { procBuilder.CreateDelete(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.InsertMetadata)) { procBuilder.CreateInsertMetadata(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.UpdateMetadata)) { procBuilder.CreateUpdateMetadata(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.DeleteMetadata)) { procBuilder.CreateDeleteMetadata(); } if (procBuilder.NeedToCreateProcedure(DbCommandType.Reset)) { procBuilder.CreateReset(); } if (this.UseBulkProcedures && procBuilder.NeedToCreateType(DbCommandType.BulkTableType)) { procBuilder.CreateTVPType(); procBuilder.CreateBulkInsert(); if (hasMutableColumns) { procBuilder.CreateBulkUpdate(); } procBuilder.CreateBulkDelete(); } } finally { if (!alreadyOpened) { connection.Close(); } } }
public DataTable ExecuteDataTable(DbCommand command, int startRecord, int maxRecords, DbConnection connection) { OnExecuteCommand(command); command.Connection = connection; DbDataAdapter adapter = Factory.CreateDataAdapter(); adapter.SelectCommand = command; DataTable dt = new DataTable(); if (startRecord > 0 || maxRecords > 0) adapter.Fill(startRecord, maxRecords, dt); else adapter.Fill(dt); return dt; }
public override void InitPipeline(ISpider spider) { if (string.IsNullOrEmpty(ConnectString)) { if (UpdateConnectString == null) { throw new SpiderException("Can't find ConnectString or IUpdateConnectString."); } else { for (int i = 0; i < 5; ++i) { try { ConnectString = UpdateConnectString.GetNew(); break; } catch (Exception e) { spider.Log("Update ConnectString failed.", LogLevel.Error, e); Thread.Sleep(1000); } } if (string.IsNullOrEmpty(ConnectString)) { throw new SpiderException("Can't updadate ConnectString via IUpdateConnectString."); } } } base.InitPipeline(spider); foreach (var metadata in DbMetadatas.Values) { if (!metadata.IsInsertModel) { continue; } NetworkCenter.Current.Execute("db-init", () => { using (DbConnection conn = CreateConnection()) { var command = conn.CreateCommand(); command.CommandText = GetIfSchemaExistsSql(metadata, conn.ServerVersion); if (Convert.ToInt16(command.ExecuteScalar()) == 0) { command.CommandText = GetCreateSchemaSql(metadata, conn.ServerVersion); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); } command.CommandText = GetCreateTableSql(metadata); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); } }); } }
public int ExecuteNonQuery(DbCommand command, DbConnection connection) { OnExecuteCommand(command); command.Connection = connection; return command.ExecuteNonQuery(); }
public DerpyDbHistoryContext(DbConnection dbConnection, string defaultSchema) : base(dbConnection, defaultSchema) { }
///<summary> /// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured ///<summary> public static FR_L5AC_GDSfDID_1214_Array Invoke(DbConnection Connection, DbTransaction Transaction, P_L5AC_GDSfDID_1214 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { return(Invoke(Connection, Transaction, null, Parameter, securityTicket)); }
protected override DataTable CheckConstraints(string tableName, DbConnection connection) { string sqlCommand = GetCheckSql(); return(CommandForTable(tableName, connection, CheckConstraintsCollectionName, sqlCommand)); }
public int AddNS(NotaSalida entity) { int id; int idKardex; int idND; int idStock; using (DbConnection conexion = _database.CreateConnection()) { conexion.Open(); using (var transaction = conexion.BeginTransaction(IsolationLevel.Serializable)) { try { using (var comando = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_NOTA_SALIDA_INSERT"))) { _database.AddInParameter(comando, "@nsalc_numero_nota_salida", DbType.String, entity.nsalc_numero_nota_salida); _database.AddInParameter(comando, "@almac_icod_almacen", DbType.Int32, entity.almac_icod_almacen); _database.AddInParameter(comando, "@nsalc_iid_motivo", DbType.Int32, entity.nsalc_iid_motivo); _database.AddInParameter(comando, "@nsalc_fecha_nota_salida", DbType.String, entity.nsalc_fecha_nota_salida_); _database.AddInParameter(comando, "@tdocc_icod_tipo_doc", DbType.Int32, entity.tdocc_icod_tipo_doc); _database.AddInParameter(comando, "@nsalc_referencia", DbType.String, entity.nsalc_referencia); _database.AddInParameter(comando, "@nsalc_observaciones", DbType.String, entity.nsalc_observaciones); _database.AddInParameter(comando, "@nsalc_usuario_crea", DbType.String, entity.UsuarioCreacion); _database.AddInParameter(comando, "@nsalc_pc_crea", DbType.String, WindowsIdentity.GetCurrent().Name); _database.AddInParameter(comando, "@nsalc_ilag_estado", DbType.Int32, entity.Estado); _database.AddOutParameter(comando, "@Response", DbType.Int32, 11); _database.ExecuteNonQuery(comando, transaction); id = Convert.ToInt32(_database.GetParameterValue(comando, "@Response")); if (id == 0) { throw new Exception("Error al ingresar Nota Ingreso"); } } foreach (var itemdetalle in entity.listaDetalleNS) { using (var comandoKardex = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_KARDEX_INSERT"))) { _database.AddInParameter(comandoKardex, "@kardc_fecha_movimiento", DbType.String, entity.nsalc_fecha_nota_salida_); _database.AddInParameter(comandoKardex, "@ningc_icod_nota_ingreso", DbType.Int32, id); _database.AddInParameter(comandoKardex, "@almac_icod_almacen", DbType.Int32, entity.almac_icod_almacen); _database.AddInParameter(comandoKardex, "@prdc_icod_producto", DbType.Int32, itemdetalle.prdc_icod_producto); _database.AddInParameter(comandoKardex, "@kardc_icantidad_prod", DbType.Decimal, itemdetalle.nsald_cantidad); _database.AddInParameter(comandoKardex, "@tdocc_icod_tipo_doc", DbType.Int32, entity.tdocc_icod_tipo_doc); _database.AddInParameter(comandoKardex, "@kardc_numero_doc", DbType.String, entity.nsalc_numero_nota_salida); _database.AddInParameter(comandoKardex, "@kardc_tipo_movimiento", DbType.Int32, 2); _database.AddInParameter(comandoKardex, "@kardc_monto_unitario_compra", DbType.Decimal, 0); _database.AddInParameter(comandoKardex, "@kardc_iid_motivo", DbType.Int32, entity.nsalc_iid_motivo); _database.AddInParameter(comandoKardex, "@kardc_beneficiario", DbType.String, entity.nsalc_referencia); _database.AddInParameter(comandoKardex, "@kardc_observaciones", DbType.String, entity.nsalc_observaciones); _database.AddInParameter(comandoKardex, "@kardc_vusuario_crea", DbType.String, entity.UsuarioCreacion); _database.AddInParameter(comandoKardex, "@kardc_vpc_crea", DbType.String, WindowsIdentity.GetCurrent().Name); _database.AddInParameter(comandoKardex, "@kardc_ilag_estado", DbType.Int32, entity.Estado); _database.AddOutParameter(comandoKardex, "@Response", DbType.Int32, 11); _database.ExecuteNonQuery(comandoKardex, transaction); idKardex = Convert.ToInt32(_database.GetParameterValue(comandoKardex, "@Response")); if (idKardex == 0) { throw new Exception("Error al ingresar kardex"); } } using (var comando = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_NOTA_SALIDA_DETALLE_INSERT"))) { _database.AddInParameter(comando, "@nsalc_icod_nota_salida", DbType.Int32, id); _database.AddInParameter(comando, "@nsald_nro_item", DbType.String, itemdetalle.nsald_nro_item); _database.AddInParameter(comando, "@prdc_icod_producto", DbType.Int32, itemdetalle.prdc_icod_producto); _database.AddInParameter(comando, "@nsald_cantidad", DbType.Decimal, itemdetalle.nsald_cantidad); _database.AddInParameter(comando, "@kardc_icod_correlativo", DbType.Int32, idKardex); _database.AddInParameter(comando, "@nsald_usuario_crea", DbType.String, entity.UsuarioCreacion); _database.AddInParameter(comando, "@nsald_pc_crea", DbType.String, WindowsIdentity.GetCurrent().Name); _database.AddInParameter(comando, "@nsald_ilag_estado", DbType.Int32, entity.Estado); _database.AddOutParameter(comando, "@Response", DbType.Int32, 11); _database.ExecuteNonQuery(comando, transaction); idND = Convert.ToInt32(_database.GetParameterValue(comando, "@Response")); if (idND == 0) { throw new Exception("Error al ingresar nota de ingreso detalle"); } } using (var ComandoStock = _database.GetStoredProcCommand(string.Format("{0}{1}", ConectionStringRepository.EsquemaName, "SGE_STOCK_UPDATE"))) { _database.AddInParameter(ComandoStock, "@almac_icod_almacen", DbType.Int32, entity.almac_icod_almacen); _database.AddInParameter(ComandoStock, "@prdc_icod_producto", DbType.Int32, itemdetalle.prdc_icod_producto); _database.AddInParameter(ComandoStock, "@stocc_stock_producto", DbType.Decimal, itemdetalle.nsald_cantidad); _database.AddInParameter(ComandoStock, "@stocc_ilag_estado", DbType.Int32, entity.Estado); _database.AddInParameter(ComandoStock, "@tipo_movimiento", DbType.Int32, 2); _database.AddOutParameter(ComandoStock, "@Response", DbType.Int32, 11); _database.ExecuteNonQuery(ComandoStock, transaction); idStock = Convert.ToInt32(_database.GetParameterValue(ComandoStock, "@Response")); if (idStock == 0) { throw new Exception("Error al ingresar stock"); } } } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw new Exception(ex.Message); } } } return(id); }
protected override DataTable StoredProcedureArguments(string storedProcedureName, DbConnection connection) { var argReader = new PostgreSqlArgumentReader(Factory, Owner); var dt = argReader.StoredProcedureArguments(storedProcedureName, connection); dt.TableName = ProcedureParametersCollectionName; return(dt); }
public override IDataBaseSchema GetDataBaseSchema(DbConnection connection) { return(new MsSqlCeDataBaseSchema(connection)); }
///<summary> /// Invokes the method for the given Connection, and Transaction, leaving them open/not commited if no exceptions occured ///<summary> public static FR_Base Invoke(DbConnection Connection, DbTransaction Transaction, P_L5SQ_DU_1137 Parameter, CSV2Core.SessionSecurity.SessionSecurityTicket securityTicket = null) { return(Invoke(Connection, Transaction, null, Parameter, securityTicket)); }