public static DataTable GetHandoverLogByHandoverLogId(int handoverLogId) { string connectionString = ConnectionStringFactory.GetNXJCConnectionString(); DataSet ds = new DataSet(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = connection.CreateCommand(); command.CommandText = @"SELECT WorkingTeamShiftLog.*, StaffInfo.Name AS ChargeManName, ProductLine.Name AS ProductLineName, WorkingTeam.Name AS WorkingTeamName FROM WorkingTeam INNER JOIN StaffInfo INNER JOIN WorkingTeamShiftLog ON StaffInfo.ID = WorkingTeamShiftLog.ChargeManID INNER JOIN ProductLine ON WorkingTeamShiftLog.ProductLineID = ProductLine.ID ON WorkingTeam.ID = WorkingTeamShiftLog.WorkingTeamID WHERE (WorkingTeamShiftLog.ID = @workingTeamShiftLogID)"; command.Parameters.Add(new SqlParameter("workingTeamShiftLogID", handoverLogId)); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(ds); } return ds.Tables[0]; }
public void PersistUpdateOf(IAggregateRoot entity) { Account account = entity as Account; string sql = "UPDATE Account SET Balance=@Balance WHERE Id=@Id"; SqlParameter parameter1 = new SqlParameter("@Id", account.Id); SqlParameter parameter2= new SqlParameter("@Balance", account.Balance); using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.Add(parameter1); cmd.Parameters.Add(parameter2); int affectedCount = cmd.ExecuteNonQuery(); if (affectedCount <= 0) { throw new Exception("update Account failed"); } } } }
// This function will be used to execute CUD(CRUD) operation of parameterized commands internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars) { int result = 0; using (SqlConnection con = new SqlConnection(CONNECTION_STRING)) { using (SqlCommand cmd = con.CreateCommand()) { cmd.CommandType = cmdType; cmd.CommandText = CommandName; cmd.Parameters.AddRange(pars); try { if (con.State != ConnectionState.Open) { con.Open(); } result = cmd.ExecuteNonQuery(); } catch { throw; } } } return (result > 0); }
// This function will be used to execute R(CRUD) operation of parameterized commands internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param) { DataTable table = new DataTable(); using (SqlConnection con = new SqlConnection(CONNECTION_STRING)) { using (SqlCommand cmd = con.CreateCommand()) { cmd.CommandType = cmdType; cmd.CommandText = CommandName; cmd.Parameters.AddRange(param); try { if (con.State != ConnectionState.Open) { con.Open(); } using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(table); } } catch { throw; } } } return table; }
public bool CreateUser(UserInfo userInfo) { var salt = _helper.GenerateSalt(); var pas = _helper.EncodePassword(userInfo.Password, salt); using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString)) { using (var command = connection.CreateCommand()) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "sp_CreateUser"; command.Parameters.AddWithValue("@Login", userInfo.Login).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Password", pas).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@PasswordSalt", salt).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Email", userInfo.Email).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Fio", userInfo.Fio).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Address", userInfo.Address).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Phone", userInfo.Phone).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Mobile", userInfo.Mobile).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Country", userInfo.Country).SqlDbType = SqlDbType.Char; command.Parameters.AddWithValue("@Zip", userInfo.Zip).SqlDbType = SqlDbType.NVarChar; var retParam = command.Parameters.AddWithValue("@Return", SqlDbType.Int); retParam.Direction = ParameterDirection.ReturnValue; connection.Open(); command.ExecuteNonQuery(); return (int)retParam.Value == 1; } } }
public IEnumerable<UserInfoShort> EnumerateClients(string fio, string login, int pageNumber, int countPerPage) { using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString)) { using (var command = connection.CreateCommand()) { command.CommandType = CommandType.StoredProcedure; command.CommandText = "sp_EnumerateClients"; command.Parameters.AddWithValue("@FIO", fio ?? (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@Login", login != null ? login.ToLower() : (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar; command.Parameters.AddWithValue("@PageNumber", pageNumber).SqlDbType = SqlDbType.Int; command.Parameters.AddWithValue("@CountPerPage", countPerPage).SqlDbType = SqlDbType.Int; connection.Open(); using (var reader = command.ExecuteReader()) { var lst = new List<UserInfoShort>(); while (reader.Read()) { lst.Add(new UserInfoShort { UserId = (Guid)reader["UserId"], CounOfCardAccounts = (int)reader["CounOfCardAccounts"], FIO = (string)reader["FIO"], Login = (string)reader["Login"], RegistrationDate = (DateTime)reader["RegistrationDate"] }); } return lst; } } } }
public Response DB(dynamic parameters) { ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["Default"]; var sb = new StringBuilder("<html><body><pre>"); try { if (null != css && false == String.IsNullOrWhiteSpace(css.ConnectionString)) { using (var conn = new SqlConnection(css.ConnectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT GETDATE()"; sb.AppendLine(cmd.ExecuteScalar().ToString()); } using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT @@VERSION"; sb.AppendLine(cmd.ExecuteScalar().ToString()); } } } } catch (Exception ex) { sb.Append(ex.Message); } sb.Append("</pre></body></html>"); return sb.ToString(); }
public bool altaRol(Rol rol) { using(TransactionScope ts = new TransactionScope()) using(SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString)) using (SqlCommand cmd = conn.CreateCommand()) try { cmd.CommandText = "INSERT INTO BONDIOLA.Rol (nombre) VALUES (@nombreRol)"; cmd.Parameters.Add(new SqlParameter("@nombreRol", rol.nombre)); conn.Open(); cmd.ExecuteNonQuery(); foreach (string funcionalidad in rol.funcionalidades) { SqlCommand cmdFuncionalidad = conn.CreateCommand(); cmdFuncionalidad.CommandType = CommandType.StoredProcedure; cmdFuncionalidad.CommandText = "BONDIOLA.altaFuncionalidadRol"; cmdFuncionalidad.Parameters.AddWithValue("@funcionalidad", funcionalidad); cmdFuncionalidad.Parameters.AddWithValue("@rol", rol.nombre); cmdFuncionalidad.ExecuteNonQuery(); } ts.Complete(); MessageBox.Show("Rol guardado con éxito."); return true; } catch (SqlException ex) { if (ex.Message.Contains("IX_Rol")) MessageBox.Show("Ya existe un rol en el sistema con el nombre ingresado"); else MessageBox.Show(ex.Message); return false; } }
public void FixtureTearDown() { // Delete database SqlConnection cn = new SqlConnection(ConnString); cn.Open(); SqlCommand cmd = cn.CreateCommand(); cmd.CommandText = "alter database [ScrewTurnWikiTest] set single_user with rollback immediate"; try { cmd.ExecuteNonQuery(); } catch(SqlException sqlex) { Console.WriteLine(sqlex.ToString()); } cmd = cn.CreateCommand(); cmd.CommandText = "drop database [ScrewTurnWikiTest]"; try { cmd.ExecuteNonQuery(); } catch(SqlException sqlex) { Console.WriteLine(sqlex.ToString()); } cn.Close(); }
public void FixtureTearDown() { // Delete database SqlConnection cn = new SqlConnection(ConnString); cn.Open(); SqlCommand cmd = cn.CreateCommand(); cmd.CommandText = "alter database [ScrewTurnWikiTest] set single_user with rollback immediate"; try { cmd.ExecuteNonQuery(); } catch(SqlException sqlex) { Console.WriteLine(sqlex.ToString()); } cmd = cn.CreateCommand(); cmd.CommandText = "drop database [ScrewTurnWikiTest]"; try { cmd.ExecuteNonQuery(); } catch(SqlException sqlex) { Console.WriteLine(sqlex.ToString()); } cn.Close(); // This is neede because the pooled connection are using a session // that is now invalid due to the commands executed above SqlConnection.ClearAllPools(); }
protected void Associate_Authenticate(object sender, AuthenticateEventArgs e) { SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AssociateConn"].ConnectionString); SqlCommand cmd = sqlConn.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "dbo.Get_Login"; cmd.Parameters.AddWithValue("@id", AssociateLogin.UserName); cmd.Parameters.AddWithValue("@password", AssociateLogin.Password); if (sqlConn.State != ConnectionState.Open) sqlConn.Open(); object o = cmd.ExecuteScalar(); int Authenticated = Convert.ToInt32(cmd.ExecuteScalar()); if (Authenticated == 1) { HttpContext.Current.Session["UserName"] = AssociateLogin.UserName.ToString(); SqlCommand cmdRole = sqlConn.CreateCommand(); cmdRole.CommandType = System.Data.CommandType.StoredProcedure; cmdRole.CommandText = "dbo.Get_RoleByUserID"; cmdRole.Parameters.AddWithValue("@id", AssociateLogin.UserName); SqlDataReader rdr = cmdRole.ExecuteReader(); while (rdr.Read()) { HttpContext.Current.Session["Role"] = rdr["RoleDesc"].ToString(); HttpContext.Current.Session["IsAdmin"] = rdr["IsAdmin"].ToString(); HttpContext.Current.Session.Timeout = 15; } rdr.Close(); Response.Redirect("~/default.aspx"); } sqlConn.Close(); }
public void SetupFixture() { SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(UnitTests.Properties.Settings.Default.SqlServer2008); if (string.IsNullOrEmpty(connStrBuilder.DataSource) || string.IsNullOrEmpty(connStrBuilder.InitialCatalog)) { Assert.Ignore("Requires SQL Server connectionstring"); } GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices(); // Set up sample table using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008)) { conn.Open(); using(SqlCommand cmd = conn.CreateCommand()) { // The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint cmd.CommandText = "CREATE TABLE roads_ugl(ID decimal(10,0) identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOM geometry)"; cmd.ExecuteNonQuery(); } // Load data using (SharpMap.Data.Providers.ShapeFile shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile())) { shapeFile.Open(); IEnumerable<uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents()); indexes = indexes.Take(100); foreach (uint idx in indexes) { var feature = shapeFile.GetFeatureByOid(idx); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO roads_ugl(NAME, GEOM) VALUES (@Name, geometry::STGeomFromText(@Geom, @Srid))"; cmd.Parameters.AddWithValue("@Geom", feature.Geometry.AsText()); cmd.Parameters.AddWithValue("@Name", feature.Attributes["NAME"]); cmd.Parameters.AddWithValue("@Srid", shapeFile.SRID); cmd.ExecuteNonQuery(); } } } // Create spatial index using(SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE SPATIAL INDEX [IX_roads_ugl_GEOM] ON [dbo].[roads_ugl](GEOM)USING GEOMETRY_GRID WITH (BOUNDING_BOX =(-98, 40, -82, 50), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM))"; cmd.ExecuteNonQuery(); } } }
public void SetupClass() { SqlConnection = new SqlConnection(@"Data Source=(localdb)\v11.0;Integrated Security=True"); SqlConnection.Open(); string createDatabase = string.Format("if not exists(select * from sys.databases where name = '{0}') CREATE DATABASE {0};", DataBaseName); var cmd = SqlConnection.CreateCommand(); cmd.CommandText = createDatabase; cmd.ExecuteNonQuery(); cmd = SqlConnection.CreateCommand(); cmd.CommandText = "USE " + DataBaseName; cmd.ExecuteNonQuery(); }
private void ConfigureDataAdapter() { con = new SqlConnection(cs); adapter.SelectCommand = con.CreateCommand(); adapter.SelectCommand.CommandType = CommandType.StoredProcedure; adapter.SelectCommand.CommandText = "DohvatiSveTablice"; adapter.InsertCommand = con.CreateCommand(); adapter.InsertCommand.CommandType = CommandType.StoredProcedure; adapter.InsertCommand.CommandText = "DodajBolid"; SqlParameter paramIDVozac = new SqlParameter(); paramIDVozac.ParameterName = "@IDVozac"; paramIDVozac.SqlDbType = SqlDbType.Int; paramIDVozac.SourceColumn = "IDVozac"; SqlParameter paramNaziv = new SqlParameter(); paramNaziv.ParameterName = "@Naziv"; paramNaziv.SqlDbType = SqlDbType.NVarChar; paramNaziv.SourceColumn = "Naziv"; adapter.InsertCommand.Parameters.Add(paramIDVozac); adapter.InsertCommand.Parameters.Add(paramNaziv); adapter.UpdateCommand = con.CreateCommand(); adapter.UpdateCommand.CommandType = CommandType.StoredProcedure; adapter.UpdateCommand.CommandText = "UpdateBolid"; SqlParameter paramUpdateID = new SqlParameter(); paramUpdateID.ParameterName = "@ID"; paramUpdateID.SqlDbType = SqlDbType.Int; paramUpdateID.SourceColumn = "ID"; SqlParameter paramUpdateNaziv = new SqlParameter(); paramUpdateNaziv.ParameterName = "@Naziv"; paramUpdateNaziv.SqlDbType = SqlDbType.NVarChar; paramUpdateNaziv.SourceColumn = "Naziv"; adapter.UpdateCommand.Parameters.Add(paramUpdateNaziv); adapter.UpdateCommand.Parameters.Add(paramUpdateID); adapter.DeleteCommand = con.CreateCommand(); adapter.DeleteCommand.CommandType = CommandType.StoredProcedure; adapter.DeleteCommand.CommandText = "BrisiBolid"; SqlParameter paramDeleteID = new SqlParameter(); paramDeleteID.ParameterName = "@ID"; paramDeleteID.SqlDbType = SqlDbType.Int; paramDeleteID.SourceColumn = "ID"; adapter.DeleteCommand.Parameters.Add(paramDeleteID); }
public static XElement ModifyTelephoneIdentificationCode(Session session, Guid accountId, string oldCode, string newCode) { bool lastResult = false; try { Token token = SessionManager.Default.GetToken(session); string connectionString = SettingManager.Default.ConnectionString; using (SqlConnection sqlconnection = new SqlConnection(connectionString)) { SqlCommand sqlCommand = sqlconnection.CreateCommand(); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "Account_UpdateDescription"; SqlCommandBuilder.DeriveParameters(sqlCommand); sqlCommand.Parameters["@id"].Value = accountId; sqlCommand.Parameters["@oldDescription"].Value = oldCode; sqlCommand.Parameters["@newDescription"].Value = newCode; sqlCommand.ExecuteNonQuery(); int result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value; if (result == 0) { sqlCommand = sqlconnection.CreateCommand(); sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandText = string.Format("UPDATE AccountHistory SET UpdatePersonID = '{0}' WHERE ID = '{1}' AND [Description] = '{2}' AND UpdateTime = (SELECT MAX(UpdateTime) FROM AccountHistory WHERE ID='{1}' AND [Description] = '{2}')", token.UserID, accountId, newCode); sqlCommand.ExecuteNonQuery(); lastResult = true; } else { //maybe the accountId is an employee id sqlCommand = sqlconnection.CreateCommand(); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "Employee_UpdateTelephonePin"; sqlconnection.Open(); SqlCommandBuilder.DeriveParameters(sqlCommand); sqlCommand.Parameters["@id"].Value = accountId; sqlCommand.Parameters["@oldPin"].Value = oldCode; sqlCommand.Parameters["@newPin"].Value = newCode; sqlCommand.ExecuteNonQuery(); result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value; lastResult = (result == 0); } } } catch (System.Exception ex) { _Logger.Error(ex); } return XmlResultHelper.NewResult(lastResult.ToPlainBitString()); }
/// <summary> /// Restores a SQL database from a backup file. /// Warning: closes all existing connections /// </summary> /// <param name="databaseName"></param> /// <param name="backUpFile">Backup File accesible from the SQL Server</param> /// <param name="serverName">SQL server instance</param> /// <param name="userName"></param> /// <param name="password"></param> public static void Restore(string databaseName, string backUpFile, string serverName, string userName = null, string password = null) { try { Console.WriteLine("Restoring database " + databaseName + " at " + serverName + " ..."); string connString = "Data Source=" + serverName + ";Initial Catalog=master;"; if (string.IsNullOrEmpty(userName)) { connString += "Integrated Security=True;"; } else { connString += string.Format("User ID={0};Password={1};", userName, password); } using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand command = conn.CreateCommand(); command = conn.CreateCommand(); command.CommandTimeout = 3 * 60; command.CommandType = System.Data.CommandType.Text; // check if db exists command.CommandText = "SELECT COUNT(*) FROM sys.databases WHERE name = '" + databaseName + "'"; conn.Open(); if (command.ExecuteScalar() as int? == 1) { // db exists, set single user command.CommandText = "ALTER DATABASE [" + databaseName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"; command.ExecuteNonQuery(); } command.CommandText = "RESTORE DATABASE [" + databaseName + "] FROM DISK = N'" + backUpFile + "' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10"; command.ExecuteNonQuery(); conn.Close(); } Console.WriteLine("Database restored successfully"); } catch (Exception ex) { Console.WriteLine("Database restore failed: " + ex.Message); throw; } }
public void CreateCommandTests() { var connection = new SqlConnection(); var command = connection.CreateCommand("ABCDEFG"); Assert.AreEqual("ABCDEFG", command.CommandText); Assert.AreSame(connection, command.Connection); command = connection.CreateCommand("123", new { IntParam = 57, DateParam = DateTime.MaxValue }); Assert.AreEqual("123", command.CommandText); Assert.AreSame(connection, command.Connection); Assert.AreEqual(2, command.Parameters.Count); Assert.AreEqual(57, command.Parameters["IntParam"].Value); Assert.AreEqual(DateTime.MaxValue, command.Parameters["DateParam"].Value); }
public void ProcessRequest(HttpContext context) { string idStr = context.Request.Params["id"]; string name = context.Request.Params["name"]; int id = -1; string xml = null; using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SdeConnectionString"])) { if (!string.IsNullOrWhiteSpace(idStr) && int.TryParse(idStr, out id)) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = Resources.GetMetadataById; cmd.Parameters.AddWithValue("@id", id); xml = cmd.ExecuteScalar() as string; } else if (!string.IsNullOrWhiteSpace(name)) { Match match = _nameRe.Match(name); if (match.Success) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = Resources.GetMetadataByName; cmd.Parameters.AddWithValue("@databaseName", match.Groups["databaseName"].Value); cmd.Parameters.AddWithValue("@owner", match.Groups["owner"].Value); cmd.Parameters.AddWithValue("@name", match.Groups["name"].Value); xml = cmd.ExecuteScalar() as string; } } } var xsl = new XslCompiledTransform(); xsl.Load(HttpContext.Current.Server.MapPath("~/style/FGDC Plus HTML5.xsl")); var doc = new XmlDocument(); doc.LoadXml(xml); context.Response.ContentType = "text/html"; XsltArgumentList args = new XsltArgumentList(); args.AddParam("includeDublinCore", string.Empty, false); args.AddParam("externalJS", string.Empty, "scripts/fgdcPlus.js"); args.AddParam("externalCss", string.Empty, "style/fgdcPlus.css"); xsl.Transform(doc, args, context.Response.OutputStream); ////context.Response.ContentType = "text/xml"; ////context.Response.Write(xml); }
static void Main(string[] args) { string connectString = @"Data Source = ASPIRE-5560G\ZLSQL; Initial Catalog = DBSlides; Integrated Security = True"; try { SqlConnection db = new SqlConnection(); db.ConnectionString = connectString; string dmlQueryDel = "DELETE FROM student WHERE student_id > 25"; string dmlQueryIns = "INSERT INTO student (student_id, first_name, last_name, birth_date, login, section_id, year_result, course_id) VALUES (26, 'Darren', 'Aronofsky', '19650513', 'darono', 1020, 18, 'EG2110')"; SqlCommand delCmd = db.CreateCommand(); delCmd.CommandText = dmlQueryDel; db.Open(); try { int delRows = delCmd.ExecuteNonQuery(); Console.WriteLine(delRows + " lignes affectées par le DELETE"); } catch (Exception ex) { Console.WriteLine(string.Format("DELETE échoué\n{0}", ex.Message)); } db.Close(); SqlCommand insCmd = db.CreateCommand(); insCmd.CommandText = dmlQueryIns; db.Open(); try { int insRows = insCmd.ExecuteNonQuery(); Console.WriteLine(insRows + " lignes affectées par le INSERT"); } catch (Exception ex) { Console.WriteLine(string.Format("INSERT échoué\n{0}", ex.Message)); } db.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.ReadKey(); }
//Se realiza el alta de un profesional dado public bool altaProfesional(Profesional profesional) { using (SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString)) using (TransactionScope ts = new TransactionScope()) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "BONDIOLA.altaProfesional"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@nombre", profesional.nombre); cmd.Parameters.AddWithValue("@apellido", profesional.apellido); cmd.Parameters.AddWithValue("@tipoDocumento", profesional.tipoDeDocumento); cmd.Parameters.AddWithValue("@numeroDocumento", profesional.numeroDeDocumento); cmd.Parameters.AddWithValue("@fechaNacimiento", profesional.fechaDeNacimiento); cmd.Parameters.AddWithValue("@direccion", profesional.direccion); cmd.Parameters.AddWithValue("@mail", profesional.mail); cmd.Parameters.AddWithValue("@telefono", profesional.telefono); cmd.Parameters.AddWithValue("@sexo", profesional.sexo); cmd.Parameters.AddWithValue("@matricula", profesional.matricula); cmd.Parameters.AddWithValue("@nombreUsuario", profesional.usuario); cmd.Parameters.AddWithValue("@hashContrasenia", Utils.hashearSHA256(profesional.contraseña)); try { conn.Open(); int idGenerado = Convert.ToInt32(cmd.ExecuteScalar()); foreach (var especialidad in profesional.especialidades) { SqlCommand cmdEsp = conn.CreateCommand(); cmdEsp.CommandType = CommandType.StoredProcedure; cmdEsp.CommandText = "BONDIOLA.altaProfesionalEspecialidad"; cmdEsp.Parameters.AddWithValue("@idProfesional", idGenerado); cmdEsp.Parameters.AddWithValue("@especialidad", especialidad); cmdEsp.ExecuteNonQuery(); } MessageBox.Show("Operación realizada con éxito."); ts.Complete(); } catch (SqlException ex) { string error = ""; if (ex.Message.Contains("Constraint_Documento")) error = "Ya existe un profesional con el número de documento " + profesional.numeroDeDocumento; else if (ex.Message.Contains("Constraint_Matricula")) error = "Ya existe un profesional con la matrícula " + profesional.matricula; MessageBox.Show(error+Environment.NewLine+ex.Message); return false; } } return true; }
internal static SqlDataAdapter CreateAndConfigDataAdapter(DataSet dataSet, SqlConnection conn, string tableName, string selCommand, KeyValuePair<string, SqlDbType> keyCol) { var adapter = new SqlDataAdapter { SelectCommand = conn.CreateCommand() }; adapter.SelectCommand.CommandText = selCommand; adapter.TableMappings.Add("Table", tableName); adapter.Fill(dataSet); Dictionary<string, SqlDbType> cols = GetColsDictionary(dataSet, tableName); var sb = new StringBuilder(); var sbPar = new StringBuilder(); sb.AppendFormat("INSERT INTO {0} (", tableName); foreach (var col in cols) { sb.AppendFormat("{0},", col.Key); sbPar.AppendFormat("@{0},", col.Key); } sb.Remove(sb.Length - 1, 1); sbPar.Remove(sbPar.Length - 1, 1); sb.AppendFormat(") VALUES({0})", sbPar); adapter.InsertCommand = conn.CreateCommand(); adapter.InsertCommand.CommandText = sb.ToString(); AddParams(adapter.InsertCommand, cols); sb.Remove(0, sb.Length); sb.AppendFormat("UPDATE {0} SET ", tableName); if (cols.ContainsKey(keyCol.Key)) cols.Remove(keyCol.Key); foreach (KeyValuePair<string, SqlDbType> col in cols) { sb.AppendFormat("{0}=@{1},", col.Key, col.Key); } sb.Remove(sb.Length - 1, 1); cols.Add(keyCol.Key, keyCol.Value); sb.AppendFormat(" WHERE {0}=@{1}", keyCol.Key, keyCol.Key); adapter.UpdateCommand = conn.CreateCommand(); adapter.UpdateCommand.CommandText = sb.ToString(); AddParams(adapter.UpdateCommand, cols); sb.Remove(0, sb.Length); sb.AppendFormat("DELETE FROM {0} WHERE {1}=@{2}", tableName, keyCol.Key, keyCol.Key); adapter.DeleteCommand = conn.CreateCommand(); adapter.DeleteCommand.CommandText = sb.ToString(); var keyPar = new Dictionary<string, SqlDbType> { { keyCol.Key, keyCol.Value } }; AddParams(adapter.DeleteCommand, keyPar); return adapter; }
public static string GetCompanyData() { string connString = ConnectionStringFactory.NXJCConnectionString; //填写链接字符串 DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connString)) { //conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT KeyID,CementTypes,vDate,OutputFirstTeam,OutputSecondTeam,OutputThirdTeam,OutputAmountto FROM table_CementMillProductionLineMonthlyOutput"; //填写查询语句 SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); } DataGridColumnType columnType = new DataGridColumnType { ColumnText = new string[] { "ID", "日期", "水泥品种", "甲班产量", "乙班产量", "丙班产量", "合计产量" }, //填写表头及宽度 ColumnWidth = new int[] { 10, 100, 100, 100,100, 100, 100 }, ColumnType = new string[] { "", "", "", "", "", "","" } }; string result = ReportTemplateHelper.GetDataGridTemplate(dt, columnType); return result; }
private static void SetupSqlServer(Cfg.Configuration cfg) { var connStr = cfg.Properties[Cfg.Environment.ConnectionString]; using (var conn = new SqlConnection(connStr.Replace("initial catalog=nhibernate", "initial catalog=master"))) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "drop database nhibernate"; try { cmd.ExecuteNonQuery(); } catch(Exception e) { Console.WriteLine(e); } cmd.CommandText = "create database nhibernate"; cmd.ExecuteNonQuery(); } } }
private void button1_Click(object sender, EventArgs e) { string connStr = "Data Source=(local);Initial Catalog=MaoMaoFriends;Persist Security Info=True;User ID=RAY\Administrator;Password=''"; SqlConnection myconn = new SqlConnection(connStr); SqlCommand mycomm = myconn.CreateCommand(); myconn.Open(); }
protected void Button1_Click(object sender, EventArgs e) { SqlTransaction myTransaction = null; { try { SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb; User ID=ajjpsqlserverdb; Password= Devry2010;"); conn.Open(); SqlCommand command = conn.CreateCommand(); string strSQL; string txtBoxText = TextBox1.Text; txtBoxText = txtBoxText.Replace("'", "''"); myTransaction = conn.BeginTransaction(); command.Transaction = myTransaction; strSQL = "UPDATE aspnet_Membership SET Password = '******' WHERE UserID = '" + DropDownList1.SelectedValue + "'"; command.CommandType = System.Data.CommandType.Text; command.CommandText = strSQL; command.ExecuteNonQuery(); myTransaction.Commit(); command.Connection.Close(); Response.Redirect("~/User/Main.aspx"); } catch (Exception ex) { lblErr.Text = ex.Message; } } }
public static void AddShortageDetail(ShortageDetails sd) { SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); SqlCommand command; connection.Open(); try { command = connection.CreateCommand(); command.CommandText = "INSERT INTO UnileverInvoiceTrackingSystem.dbo.shortagedetails (invoiceno, shortagedetails) VALUES (@IN,@SD);"; command.Parameters.AddWithValue("@IN", Convert.ToInt64(sd.InvoiceNo)); command.Parameters.AddWithValue("@SD", sd.SD); command.ExecuteNonQuery(); } catch (Exception) { throw; } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } }
public string[] GetSubscribers(Type messageType) { using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = string.Format(@"select endpoint from [{0}] where message_type = @message_type", subscriptionsTableName); command.Parameters.AddWithValue("message_type", messageType.FullName); var endpoints = new List<string>(); using (var reader = command.ExecuteReader()) { while (reader.Read()) { endpoints.Add((string)reader["endpoint"]); } } return endpoints.ToArray(); } } }
public Account GetById(int id) { string sql = "SELECT Id, Balance FROM Account WHERE Id=@Id"; SqlParameter parameter = new SqlParameter("@Id", id); Account account = new Account(); using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); using(SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.Add(parameter); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { account.Id = id; account.Balance = Convert.ToDecimal(reader["Balance"]); } } } return account; }
public List<User> FindAll() { List<User> Users = new List<User>(); string query = "SELECT * FROM [User]"; using (SqlConnection db = new SqlConnection(CONN_STR)) { if (db.State != System.Data.ConnectionState.Open) { db.Open(); } using (SqlCommand cmd = db.CreateCommand()) { cmd.CommandText = query; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { User u = new User(); u.UserID = reader.GetInt32(reader.GetOrdinal("UserID")); u.Username = reader.GetString(reader.GetOrdinal("Username")); u.Password = reader.GetString(reader.GetOrdinal("Password")); u.IsAdmin = reader.GetBoolean(reader.GetOrdinal("IsAdmin")); Users.Add(u); } } } } return Users; }
/// <summary> /// Créer l'enregistrement en base en récupérant la valeur de la clé primaire créée automatiquement. /// </summary> /// <param string="sRequest">Chemin du dossier à ziper.</param> /// <param PNPU_PROCESS="input">Paramétres récupérés depuis le Web Service</param> /// <param string="sTable">Nom de la table impacté par l'insertion</param> /// <returns>Retourne 0 si ok, -1 en cas de problème.</returns> public static string SendTransactionWithGetLastPKid(string sRequest, PNPU_PROCESS input, string sTable) { using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli)) { string LastInsertedPK = ""; conn.Open(); SqlCommand command = conn.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = conn.BeginTransaction("CreateProcess"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = conn; command.Transaction = transaction; try { command.CommandText = sRequest; /*for (int i = 0; i < sParameters.Length; i++) * { * sParameters[i].ToString(); * command.Parameters.Add("@" + sParameters[i].ToString(), SqlDbType.VarChar, 254).Value = sParameters[i].ToString(); * }*/ command.Parameters.Add("@PROCESS_LABEL", SqlDbType.VarChar, 254).Value = input.PROCESS_LABEL; command.Parameters.Add("@IS_LOOPABLE", SqlDbType.VarChar, 254).Value = input.IS_LOOPABLE; command.ExecuteNonQuery(); command.CommandText = "SELECT IDENT_CURRENT('" + sTable + "') AS [IDENT_CURRENT]"; LastInsertedPK = command.ExecuteScalar().ToString(); transaction.Commit(); Console.WriteLine(LastInsertedPK); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); try { transaction.Rollback(); return("Commit Exception Type: {0}" + ex.GetType()); } catch (Exception ex2) { Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); return("Rollback Exception Type: {0}" + ex2.GetType()); } } return(LastInsertedPK); } }
public Usuario update(Usuario usuario) { Usuario u = null; const string SQL = ""; using (SqlConnection conexion = new System.Data.SqlClient.SqlConnection()) { SqlCommand command = conexion.CreateCommand(); } return(u); }
private void buscarRfid(string rfid) { try { con.Open(); SqlCommand query = con.CreateCommand(); query.CommandType = CommandType.Text; //Consulta sobre tarjetas de personas if (step == 1) { query.CommandText = string.Format("EXEC BUSCAR_PERSONA_PRESTAMO '" + rfid + "'"); SqlDataReader busqueda; busqueda = query.ExecuteReader(); while (busqueda.Read() == true) { lbNombre.Text = busqueda["Nombre"].ToString(); valido = 1; } } //Consulta sobre tarjetas de articulos else if (step == 2) { query.CommandText = string.Format("EXEC BUSCAR_ARTICULO_PRESTAMO '" + rfid + "'"); SqlDataReader busqueda; busqueda = query.ExecuteReader(); while (busqueda.Read() == true) { lbNombre.Text = busqueda["Nombre"].ToString(); valido = 1; infoArt[0] = busqueda["Nombre"].ToString(); infoArt[1] = busqueda["Descripcion"].ToString(); infoArt[2] = busqueda["Categoria"].ToString(); infoArt[3] = busqueda["Precio_renta"].ToString(); infoArt[4] = busqueda["Stock"].ToString(); infoArt[5] = busqueda["Disponible"].ToString(); } } else if (step == 3) { } } catch { } finally { if (con.State != ConnectionState.Closed) { con.Close(); } } }
private void OpenWithCheck(string checkQueryString) { var connectionChecked = false; var restoreTriggered = false; while (!connectionChecked) { base.Open(); try { using (var command = underlyingConnection.CreateCommand()) { command.CommandText = checkQueryString; command.ExecuteNonQuery(); } connectionChecked = true; } catch (Exception exception) { if (InternalHelpers.ShouldRetryOn(exception)) { if (restoreTriggered) { throw; } var newConnection = new SqlServerConnection(underlyingConnection.ConnectionString); try { underlyingConnection.Close(); underlyingConnection.Dispose(); } catch { } underlyingConnection = newConnection; restoreTriggered = true; continue; } throw; } } }
/// <summary> /// 包含事务对象执行Sql语句 /// </summary> /// <param name="con"></param> /// <param name="sql"></param> /// <param name="trans"></param> /// <returns></returns> public static int ExecuteSql(this System.Data.SqlClient.SqlConnection con, string sql, System.Data.SqlClient.SqlTransaction trans) { //创建SqlCommand对象 var cmd = con.CreateCommand(); //设置执行的Sql语句(语句限定insert update delete) cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; //设置事务 cmd.Transaction = trans; //执行Sql语句 return(cmd.ExecuteNonQuery()); }
private static ErrorMessageParser CreateMessageParser(SqlServerConnection connection) { bool isEnglish; using (var command = connection.CreateCommand()) { command.CommandText = LangIdQuery; isEnglish = command.ExecuteScalar().ToString() == "0"; } var templates = new Dictionary <int, string>(); using (var command = connection.CreateCommand()) { command.CommandText = MessagesQuery; using (var reader = command.ExecuteReader()) { while (reader.Read()) { ReadMessageTemplate(reader, templates); } } } return(new ErrorMessageParser(templates, isEnglish)); }
public void deleteEquipment(Equipment e) { sc.ConnectionString = ConfigurationManager.ConnectionStrings["Lab2ConnectionString"].ConnectionString; myCommand = sc.CreateCommand(); SqlParameter equipmentIDPara = new SqlParameter("@eqipmentID", SqlDbType.Int); myCommand.Parameters.Add(equipmentIDPara); equipmentIDPara.Value = e.getID(); myCommand.CommandText = @"DELETE FROM Equipment WHERE ID = @equipmentID"; sc.Open(); myCommand.ExecuteNonQuery(); sc.Close(); }
//CreateDatabase TO BE IMPLEMENTED public void CreateSqlDatabase() { string path = System.AppDomain.CurrentDomain.BaseDirectory; string jclue = path + "JClues.mdf"; string jclue_log = path + "JClues_log.ldf"; using (var connection = new System.Data.SqlClient.SqlConnection( "Data Source=(LocalDB)\\MSSQLLocalDB;Integrated Security=True;")) { connection.Open(); using (var command = connection.CreateCommand()) { string str = String.Format("CREATE DATABASE JClues ON PRIMARY " + "(NAME = JClues, " + "FILENAME = '{0}', " + "SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) " + "LOG ON (NAME = JClues_Log, " + "FILENAME = '{1}', " + "SIZE = 1MB, " + "MAXSIZE = 2048MB, " + "FILEGROWTH = 10%)", jclue, jclue_log); command.CommandText = str; try { command.ExecuteNonQuery(); } catch (SqlException e) { command.CommandText = "Drop Database JClues"; command.ExecuteNonQuery(); command.CommandText = str; command.ExecuteNonQuery(); } command.CommandText = "USE JClues"; command.ExecuteNonQuery(); str = String.Format("CREATE TABLE [dbo].[Clues] (" + "[Id] INT IDENTITY (1, 1) NOT NULL," + "[Text] NVARCHAR (MAX) NOT NULL," + "[Answer] NVARCHAR (MAX) NOT NULL," + "[Value] INT NOT NULL," + "[Category] NVARCHAR (MAX) NOT NULL," + "[DailyDouble] BIT NOT NULL," + "[Round] INT NOT NULL," + "[Airdate] SMALLDATETIME NOT NULL);"); command.CommandText = str; command.ExecuteNonQuery(); } } }
public DataTable PackingGetDatabyBOX(string BoxNumber) { using (sqlConnection4 = new SqlConnection(strSqlConnection4_608FFCPACKING)) { sqlConnection4.Open(); #region Kiem tra Prod Order trong T_information string result = "Kiem tra ProdOrder trong T_information"; SqlCommand cmdGetProdOrderSerials = sqlConnection4.CreateCommand(); cmdGetProdOrderSerials.CommandType = CommandType.StoredProcedure; cmdGetProdOrderSerials.CommandText = "PackingRecord_GetByBox"; cmdGetProdOrderSerials.Parameters.Add("@BoxNumber", SqlDbType.Char, 20); cmdGetProdOrderSerials.Parameters["@BoxNumber"].Value = BoxNumber; cmdGetProdOrderSerials.Parameters["@BoxNumber"].Direction = ParameterDirection.Input; SqlDataReader rec = cmdGetProdOrderSerials.ExecuteReader(); rec.Read(); result = rec[0].ToString(); #endregion if (result.Equals("OK")) { //TraceStepDoing("Doc thong Tinformation "); //rec.NextResult(); //rec.Read(); //strPN = rec["T_ProdOrder"].ToString().Trim(); //strProductmap = rec["T_ProductMap"].ToString().Trim(); //strPN = rec["T_Material"].ToString().Trim(); //strRev = rec["T_Revision"].ToString().Trim(); //StrDes = ""; //string tmpProductMap = getProductMapDetail(strPN, ref StrDes); //intPOQuantity = Int32.Parse(rec["T_Quantity"].ToString().Trim()); //intPOPacked = Int32.Parse(rec["T_Packed"].ToString().Trim()); sqlConnection4.Close(); } DataTable d = new DataTable(); d.Load(cmdGetProdOrderSerials.ExecuteReader()); cmdGetProdOrderSerials.Dispose(); sqlConnection4.Close(); return(d); } }
//增、更新 public static void Excsql(string ConnString, string sql) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection()) { conn.ConnectionString = ConnString; conn.Open(); using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand()) { command.CommandText = sql; command.ExecuteNonQuery(); } conn.Close(); } }
/// <summary> /// [To be supplied.] /// </summary> /// <param name="connectionString">Connection string to be used when accessing the database.</param> /// <param name="col_CustomerID">[To be supplied.]</param> public Customer(string connectionString, System.Data.SqlTypes.SqlInt32 col_CustomerID) { #if OLYMARS_DEBUG object olymarsDebugCheck = System.Configuration.ConfigurationSettings.AppSettings["OlymarsDebugCheck"]; if (olymarsDebugCheck == null || (string)olymarsDebugCheck == "True") { string DebugConnectionString = connectionString; if (DebugConnectionString == System.String.Empty) { DebugConnectionString = Bob.DataClasses.Information.GetConnectionStringFromConfigurationFile; } if (DebugConnectionString == System.String.Empty) { DebugConnectionString = Bob.DataClasses.Information.GetConnectionStringFromRegistry; } if (DebugConnectionString != String.Empty) { System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(DebugConnectionString); sqlConnection.Open(); System.Data.SqlClient.SqlCommand sqlCommand = sqlConnection.CreateCommand(); sqlCommand.CommandType = System.Data.CommandType.Text; sqlCommand.CommandText = "Select sysobjects.schema_ver from sysobjects where sysobjects.name = 'Customers'"; int CurrentRevision = (int)sqlCommand.ExecuteScalar(); sqlConnection.Close(); int OriginalRevision = ((Bob.DataClasses.OlymarsInformationAttribute)System.Attribute.GetCustomAttribute(this.GetType(), typeof(Bob.DataClasses.OlymarsInformationAttribute), false)).SqlObjectDependancyRevision; if (CurrentRevision != OriginalRevision) { throw new System.InvalidOperationException(System.String.Format("OLYMARS: This code is not in sync anymore with [{0}]. It was generated when [{0}] version was: {2}. Current [{0}] version is: {1}", "Customers", CurrentRevision, OriginalRevision)); } } } #endif this.recordWasLoadedFromDB = true; this.recordIsLoaded = false; this.connectionString = connectionString; this.lastKnownConnectionType = Bob.DataClasses.ConnectionType.ConnectionString; this.col_CustomerID = col_CustomerID; }
public static string apply_contents(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req, AccessControl.AccessManager access_manager, System.Data.SqlClient.SqlTransaction tr) { System.Guid guididx = new System.Guid(req["guididx"]); SqlCommand cmd = conn.CreateCommand(); cmd.Transaction = tr; cmd.CommandText = String.Format( @" delete from shop_item_tests where idx=(select idx from shop_item where guididx='{0}'); delete from shop_item_pdfs where idx=(select idx from shop_item where guididx='{0}'); update shop_item set parent_idx=null where parent_idx=(select idx from shop_item where guididx='{0}'); " , guididx); System.Collections.Generic.List <int> tests = new System.Collections.Generic.List <int> (); for (int i = 0; i < req.Params.Keys.Count; ++i) { string key = req.Params.Keys[i]; if (key.IndexOf("idx_", 0) == 0) { string[] a = key.Substring(4).Split('_'); if (a[0] == "1") { cmd.CommandText += String.Format("update shop_item set parent_idx=(select idx from shop_item where guididx='{0}') where idx={1};\n", guididx, a[1]); } if (a[0] == "2") { cmd.CommandText += String.Format("insert into shop_item_tests (test_idx,idx) select {1},idx from shop_item where guididx='{0}';\n", guididx, a[1]); tests.Add(Int32.Parse(a[1])); } if (a[0] == "3") { cmd.CommandText += String.Format("insert into shop_item_pdfs (pdf_idx,idx) select {1},idx from shop_item where guididx='{0}';\n", guididx, a[1]); tests.Add(Int32.Parse(a[1])); } } } cmd.ExecuteNonQuery(); /*foreach(int testid in tests) * { * access_manager.check_function_and_group(String.Format("test_{0}",testid), get_test_name_by_id_(conn,tr,testid)); * } */ return(show_item_cont(conn, req, tr)); }
/// <summary> /// 将Excel文件导入数据库中,先删除库中表再导入 /// </summary> /// <param name="excelFile"></param> /// <param name="sheetName"></param> /// <param name="connectionString"></param> public void TransferData(string excelFile, string sheetName, string connectionString) { DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";"; // string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); //如果目标表不存在则创建 string strSql = string.Format("if object_id('{0}') is not null drop table {0} if object_id('{0}') is null create table {0}(", sheetName); //以sheetName为表名 foreach (System.Data.DataColumn c in ds.Tables[0].Columns) { strSql += string.Format("[{0}] varchar(255),", c.ColumnName); } strSql = strSql.Trim(',') + ")"; using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); command.CommandText = strSql; command.ExecuteNonQuery(); sqlconn.Close(); } //用bcp导入数据 using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); count = ds.Tables[0].Rows.Count; bcp.BatchSize = count > 1000 ? ds.Tables[0].Rows.Count / 10 : 100;; //每次传输的行数 bcp.NotifyAfter = count > 100 ? ds.Tables[0].Rows.Count / 10 : 1; // 1000;//进度提示的行数 bcp.DestinationTableName = sheetName; //目标表 bcp.WriteToServer(ds.Tables[0]); } } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } }
private void upData() { using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connString)) { sqlconn.Open(); System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand(); foreach (var strSql in sqlList) { command.CommandText = strSql; command.ExecuteNonQuery(); } sqlconn.Close(); } }
// creates and prepares a new DbCommand object on a new connection public static DbCommand CreateCommand() { // string connectionString = SiteConfiguration.DbConnectionString; // Set the connection string // System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); dy(); System.Data.SqlClient.SqlConnection conn = epconn; // Create a database specific command object DbCommand comm = conn.CreateCommand(); // Set the command type to stored procedure comm.CommandType = CommandType.StoredProcedure; // Return the initialized command object return(comm); }
public static string list_q_in_test(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req) { int test_id = Int32.Parse(req["test_id"]); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = String.Format( @"SELECT SetsToQuestions.QuestionId, Questions.text FROM Tests INNER JOIN TestContents ON Tests.Id = TestContents.TestId INNER JOIN QuestionSets ON TestContents.QuestionSetId = QuestionSets.Id INNER JOIN SetsToQuestions ON QuestionSets.Id = SetsToQuestions.SetId INNER JOIN Questions ON SetsToQuestions.QuestionId = Questions.Id WHERE (Tests.Id = {0}) ORDER BY TestContents.QuestionSetOrder, SetsToQuestions.QuestionOrder" , test_id); string ret = ""; bool f = true; using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (!f) { ret += ";"; } if (f) { f = false; } ret += reader[0].ToString(); ret += "||"; string s = reader[1].ToString(); if (s.Length != 0) { ret += s.Substring(0, s.Length >= 32?32:s.Length) + "..."; } else { ret += s + "..."; } } } return(ret); }
public static string save_item_descr(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req, System.Data.SqlClient.SqlTransaction tr) { string descr = req["descr"]; System.Guid guididx = new System.Guid(req["guididx"]); SqlCommand cmd = conn.CreateCommand(); cmd.Transaction = tr; cmd.CommandText = String.Format("update shop_item set descr='{1}' where guididx='{0}';", req["guididx"], req["descr"]); cmd.ExecuteNonQuery(); return("ok"); }
//get all items in the Numbers table #region access functions for the numbers table public List <NumberDAL> GetAllNumbers() { //Ensure connected opens the connection to the database if it is not already opended EnsureConnected(); //create an empty list to return List <NumberDAL> rv = new List <NumberDAL>(); //create a sql command object using the existing and open connection. we know it is open //because of ensureconnection(); SqlCommand localcommand = MyConnection.CreateCommand(); //configure the command object. we are sending literal sql to execute, so use text as the type // and select * from numbers since we want all numbers localcommand.CommandType = System.Data.CommandType.Text; localcommand.CommandText = "Select * from Numbers"; //start the query SqlDataReader reader = localcommand.ExecuteReader(); //reader.read positions us to the first record returned by the sql query //and it returns false when there are no more records, and true when the record is postioned int IDPosition = 0; int NamePosition = 0; int DSPosition = 0; int FSPosition = 0; bool first = true; while (reader.Read()) { if (first) { first = false; IDPosition = reader.GetOrdinal("ID"); NamePosition = reader.GetOrdinal("Name"); DSPosition = reader.GetOrdinal("Doublestuff"); FSPosition = reader.GetOrdinal("Floatstuff"); } NumberDAL r = new NumberDAL(); r.ID = reader.GetInt32(IDPosition);// this involves boxing, unboxing and garbage generation r.Name = (string)reader[NamePosition]; //r.Doublstuff = (double)reader[DSPosition]; r.Doublstuff = reader.GetDouble(DSPosition); r.Floatstuff = reader.GetFloat(FSPosition); //r.Floatstuff = (float)reader[FSPosition]; rv.Add(r); } reader.Close(); return(rv); }
/// <summary> /// creates an empty database with the name specified in the connection string /// </summary> public static void CreateDatabase(string connectionString) { SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString); string dbName = builder.InitialCatalog; builder.InitialCatalog = string.Empty; using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(builder.ConnectionString)) { conn.Open(); var command = conn.CreateCommand(); command.CommandType = System.Data.CommandType.Text; command.CommandText = "use [master]"; command.ExecuteNonQuery(); command.CommandText = string.Format("create database {0} collate SQL_Latin1_General_CP1_CS_AS", dbName); command.ExecuteNonQuery(); } }
public static string show_item_descr(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req, System.Data.SqlClient.SqlTransaction tr) { SqlCommand cmd = conn.CreateCommand(); cmd.Transaction = tr; cmd.CommandText = String.Format("select descr from shop_item where guididx='{0}';", req["guididx"]); System.Object obj = cmd.ExecuteScalar(); String value = obj.GetType() == typeof(System.DBNull)?"":obj.ToString(); string ret = String.Format(@"<div> <form name='item_descr' action='1.axp' method='get' onsubmit='javascript:save_text({0}); return false;'> <textarea id='HereIsTheText'> {1}</textarea></form> </div>", "\"" + req["guididx"] + "\"", value); return(ret); }
static public bool SetModalityPerformedProcedureStep(int procedureStepId, XmlElement sequenceXmlDoc) { SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = MwlMppsDBBroker.Properties.Settings.Default.ConnectionString; SqlTransaction transaction = null; try { conn.Open(); transaction = conn.BeginTransaction(); string commandText = string.Format("EXEC dbo.sp_SetModalityPerformedProcedureStep @psId = {0} ", procedureStepId); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = commandText; int recordAffected = cmd.ExecuteNonQuery(); bool success = CreatePerformedSeriesSequence(sequenceXmlDoc, transaction); if (success) { transaction.Commit(); } else { transaction.Rollback(); } return(true); } catch (Exception ex) { Platform.Log(LogLevel.Error, ex); if (transaction != null) { transaction.Rollback(); } return(false); } finally { if (conn.State != System.Data.ConnectionState.Closed) { conn.Close(); } } }
public static void RunSP(string spName, List <SqlDbParameter> inPars, ref DataTable destTable) { SqlDataAdapter DBAdapter = null; SqlConnection DBConn = new System.Data.SqlClient.SqlConnection(ConnectionString); DBConn.Open(); try { var DBComm = DBConn.CreateCommand(); DBComm.CommandType = CommandType.StoredProcedure; DBComm.CommandText = spName; DBAdapter = new SqlDataAdapter(DBComm); if (inPars.Count > 0) { foreach (var sqlDbParameter in inPars) { DBAdapter.SelectCommand.Parameters.AddWithValue("@" + sqlDbParameter.ParName, sqlDbParameter.ParValue); } } var ds = new DataSet(); DBAdapter.Fill(ds); DBAdapter.Dispose(); DBComm.Dispose(); destTable = ds.Tables[0]; } catch (SqlException ex) { } catch (Exception ex) { } finally { if (DBAdapter != null) { DBAdapter.Dispose(); } DBConn.Close(); } }
private void button8_Click(object sender, EventArgs e) { if (MessageBox.Show("ZEKER WETEN>>>!!!!!!!", "MegaTabelMaak", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK) { var conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = "Data Source=SQL-SERVER;Initial Catalog=zeebregtsdb;User ID=daan;Password=Bl22sk22k!"; conn.Open(); for (int i = 0; i < 100000; i++) { var r = new Random(DateTime.Now.Second); string sql = "INSERT INTO StresTestTable ([Spoef]) VALUES (" + r.GetHashCode().ToString() + ")"; SqlCommand command = conn.CreateCommand(); command.CommandText = sql; command.ExecuteNonQuery(); } conn.Close(); } }
//查询 public static System.Data.DataSet Query(string ConnString, string sql) { System.Data.DataSet result = new System.Data.DataSet(); using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection()) { conn.ConnectionString = ConnString; conn.Open(); using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand()) { command.CommandText = sql; using (System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(command)) { adp.Fill(result); } } conn.Close(); } return(result); }
private string CreateAdministrator(string connectionstr) { System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionstr); dbConnection.Open(); System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand(); dbCommand.Connection = dbConnection; dbCommand.CommandType = System.Data.CommandType.Text; dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY"; int num = System.Convert.ToInt32(dbCommand.ExecuteScalar()); dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", "admin")); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt("123456"))); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", "*****@*****.**")); dbCommand.ExecuteNonQuery(); dbConnection.Close(); return(""); }
public static void ClassInitialize(TestContext testContext) { path = Path.Combine(new FileInfo(typeof(DBTests).Assembly.Location).Directory.FullName, "UnitTestData.mdf"); if (File.Exists(path)) { File.Delete(path); } DatabaseUtil.CreateSqlDatabase(path); conn = new System.Data.SqlClient.SqlConnection(connstr + path); conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandText = OgcConformanceMap.DropTables; cmd.ExecuteNonQuery(); cmd.CommandText = OgcConformanceMap.CreateTables; cmd.ExecuteNonQuery(); cmd.CommandText = OgcConformanceMap.CreateRows; cmd.ExecuteNonQuery(); }
public void Commit() { using (var connection = new System.Data.SqlClient.SqlConnection(_connectionString.FormattedConnectionString)) { connection.Open(); var entitySets = ConvertToBatches(_entities, 200); // limitation of num rows allowed to insert in one call foreach (var entitySet in entitySets) { string sql = "INSERT TestEntities(TestDate , TestInt, TestString) VALUES" + String.Join(",", entitySet.Select(e => String.Format("('{0}',{1},'{2}')", e.TestDate.ToString("yyyy-MM-ddTHH:mm:ss.fff"), e.TestInt, e.TestString))); var cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; cmd.ExecuteNonQuery(); } } }
private String getDBVersion() { String version = "Access error"; try { SqlConnection conn = new System.Data.SqlClient.SqlConnection(BaseConnectionStringBuilder.ConnectionString); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT count(*) From " + TestTable; version = cmd.ExecuteScalar().ToString(); conn.Close(); } catch (Exception ex) { } return(version); }
private void ReadInforFromT_Information(string PO, ref string strPN, ref string strRev, ref string StrDes, ref string strProductmap) { using (sqlConnection4 = new SqlConnection(strSqlConnection4_608FFCPACKING)) { sqlConnection4.Open(); #region Kiem tra Prod Order trong T_information string result = "Kiem tra ProdOrder trong T_information"; PO = (PO).PadLeft(12, '0'); SqlCommand cmdGetProdOrderSerials = sqlConnection4.CreateCommand(); cmdGetProdOrderSerials.CommandType = CommandType.StoredProcedure; cmdGetProdOrderSerials.CommandText = "ame_T_getProdOrderInfo";; cmdGetProdOrderSerials.Parameters.Add("@ProdOrder", SqlDbType.Char, 20); cmdGetProdOrderSerials.Parameters["@ProdOrder"].Value = PO; cmdGetProdOrderSerials.Parameters["@ProdOrder"].Direction = ParameterDirection.Input; SqlDataReader rec = cmdGetProdOrderSerials.ExecuteReader(); rec.Read(); result = rec[0].ToString(); #endregion if (result.Equals("OK")) { //TraceStepDoing("Doc thong Tinformation "); rec.NextResult(); rec.Read(); strPN = rec["T_ProdOrder"].ToString().Trim(); strProductmap = rec["T_ProductMap"].ToString().Trim(); strPN = rec["T_Material"].ToString().Trim(); strRev = rec["T_Revision"].ToString().Trim(); StrDes = ""; //string tmpProductMap = getProductMapDetail(strPN,ref StrDes); //intPOQuantity = Int32.Parse(rec["T_Quantity"].ToString().Trim()); //intPOPacked = Int32.Parse(rec["T_Packed"].ToString().Trim()); sqlConnection4.Close(); } } }
private bool CreateAdministrator(out string errorMsg) { System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()); dbConnection.Open(); System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand(); dbCommand.Connection = dbConnection; dbCommand.CommandType = System.Data.CommandType.Text; dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY"; int num = System.Convert.ToInt32(dbCommand.ExecuteScalar()); dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())"; dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username)); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt(this.password))); dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email)); dbCommand.ExecuteNonQuery(); dbConnection.Close(); errorMsg = null; return(true); }
private void boutonSave_Click(object sender, EventArgs e) { try { DateTime dateCommentaire = Convert.ToDateTime(inputDate.Text); SqlConnection db = new SqlConnection(); db.ConnectionString = connectionString; SqlCommand sql = db.CreateCommand(); sql.CommandText = "SELECT MAX(Clé)+1 FROM Inscrits"; db.Open(); object id = sql.ExecuteScalar(); db.Close(); string query = "set dateformat mdy;" + "INSERT INTO CoachingComments VALUES ('" + _cleSession + "',null,'" + inputCommentaire.Text + "','" + dateCommentaire.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US")) + "')"; SqlCommand cmd = db.CreateCommand(); cmd.CommandText = query; db.Open(); cmd.ExecuteNonQuery(); db.Close(); } catch (SqlException sqlex) { MessageBox.Show("Une erreur SQL est survenue: " + sqlex.Message, "Erreur 7", MessageBoxButtons.OK, MessageBoxIcon.Error); } catch (Exception ex) { MessageBox.Show("Une erreur est survenue: " + ex.Message, "Erreur 8", MessageBoxButtons.OK, MessageBoxIcon.Error); } this.Close(); }