private void btnAddNewStock_Click(object sender, EventArgs e) { tb_log.Text = string.Empty; SqlConnection conn = new SqlConnection(); SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(); sb.DataSource = tb_server.Text; sb.InitialCatalog = tb_database.Text; if (cb_integratedSecurity.Checked == true) { sb.IntegratedSecurity = true; } else { sb.IntegratedSecurity = false; sb.UserID = tb_username.Text; sb.Password = tb_password.Text; } conn.ConnectionString = sb.ToString(); tb_log.AppendText(DateTime.Now + " - Processing: " + tb_newSymbol.Text + "\n"); StockNames stockNames = new StockNames(conn); Stock s = new Stock(); s.symbol = tb_newSymbol.Text; s.name = tb_newName.Text; s.exchange = cb_exchange.SelectedItem.ToString(); stockNames.CreateNewStock(s); tb_log.AppendText(DateTime.Now + " - Processed: " + tb_newSymbol.Text + "\n"); tb_log.AppendText(DateTime.Now + " - ***FINISHED PROCESSING***"); }
/// <summary> /// Creates sql connection object with sql login /// </summary> public static SqlConnection CreateSqlConnectionWithSqlLogin(string dataSource, string username, string password, string databaseName = null) { SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(); SqlConnection sqlConnection = null; connectionStringBuilder.DataSource = dataSource; connectionStringBuilder.UserID = username; connectionStringBuilder.Password = password; if (databaseName != null) { connectionStringBuilder.InitialCatalog = databaseName; } try { sqlConnection = new SqlConnection(connectionStringBuilder.ConnectionString); } catch (SqlException e) { BootstrapperManager.Instance.Bootstrapper.Engine.Log(LogLevel.Error, string.Format("[CreateSqlConnectionWithSqlLogin] {0}", e.Message)); } return sqlConnection; }
/// <summary> /// Creates a database on the server. /// </summary> /// <param name="sqlProviderConnectionString">Connection string</param> /// <returns>Error</returns> private string CreateDatabase(string sqlProviderConnectionString) { try { //parse database name var builder = new SqlConnectionStringBuilder(sqlProviderConnectionString); var databaseName = builder.InitialCatalog; //now create connection string to 'master' dabatase. It always exists. builder.InitialCatalog = "master"; var masterCatalogConnectionString = builder.ToString(); string query = string.Format("CREATE DATABASE [{0}] COLLATE SQL_Latin1_General_CP1_CI_AS", databaseName); using (var conn = new SqlConnection(masterCatalogConnectionString)) { conn.Open(); using (var command = new SqlCommand(query, conn)) { command.ExecuteNonQuery(); } } return string.Empty; } catch (Exception ex) { return string.Format("An error occured when creating database: {0}", ex.Message); } }
private void ButtonSuchen_Click(object sender, RoutedEventArgs e) { SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder(); bldr.DataSource = @"(local)"; bldr.InitialCatalog = "NwindSQL"; bldr.IntegratedSecurity = true; //Data Source=RG6\RG6_SQL05;Initial Catalog=Northwind;Integrated Security=True SqlConnection con = new SqlConnection(bldr.ConnectionString); con.Open(); String sqlString = "Select ProductId, ProductName, UnitPrice From Products Where ProductId = " + textBoxProductId.Text; SqlCommand cmd = con.CreateCommand(); cmd.CommandText = sqlString; SqlDataReader reader = cmd.ExecuteReader(); grid1.DataContext = null; if (reader.Read()) { Product prod = new Product((int)reader[0], (string)reader[1], (decimal)reader[2]); grid1.DataContext = prod; } else { //grid1.DataContext = null; textBoxProductId.Text = textBoxProductId.Text + ":Satz nicht gefunden"; //txtBlockProdName.Text = String.Empty; //txtBlockUnitPrice.Text = String.Empty; } reader.Close(); con.Close(); }
public static void m3() { SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder(); sqlConnectionStringBuilder.DataSource = @"RoXaSDTD-PC\SQLEXPRESS"; sqlConnectionStringBuilder.InitialCatalog = "PlaylistManager"; sqlConnectionStringBuilder.UserID = "Rafael"; sqlConnectionStringBuilder.Password = "******"; sqlConnectionStringBuilder.IntegratedSecurity = true; string connectionString = sqlConnectionStringBuilder.ConnectionString; Builder builder = new Builder( typeof(SqlDataMapper<>), new Object[] { connectionString }, typeof(PropertyColumnMapper), typeof(MultipleConnectionPolicy)); IDataMapper<Playlist> playlistMapper = builder.Build<Playlist>(); IConnectionPolicy policy = playlistMapper.GetConnectionPolicy(); Playlist p = playlistMapper.GetAll().First(); p.name = "teste"; playlistMapper.Update(p); Playlist p2 = playlistMapper.GetAll().First(); p2.name = "JoanaPlaylist"; playlistMapper.Update(p2); }
/// <inheritdoc/> public IConnectionProvider CreateProvider( IDictionary<string, string> options) { string connection_string; if (options.TryGetValue(kConnectionStringOption, out connection_string)) { return new SqlConnectionProvider(connection_string); } SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = GetOption(kServerOption, options); // We try to get the user name information using the "login" key for // backward compatibility. string user_id; if (!options.TryGetValue(kLoginOption, out user_id)) { user_id = GetOption(kUserNameOption, options); } builder.UserID = user_id; builder.Password = GetOption(kPasswordOption, options); string catalog; if (options.TryGetValue(kInitialCatalogOption, out catalog)) { builder.InitialCatalog = catalog; } return new SqlConnectionProvider(builder.ConnectionString); }
public static IDataReader DataReaderFromSql(string sql) { if (String.IsNullOrWhiteSpace(sql)) throw new ArgumentException("String was null or empty.", "sql"); var connectionString = new SqlConnectionStringBuilder { DataSource = @".\SQLEXPRESS", InitialCatalog = "AdventureWorksDW2008R2", IntegratedSecurity = true }; var connection = new SqlConnection(connectionString.ConnectionString); try { connection.Open(); } catch (SqlException e) { throw new Exception("These tests require data from the 'AdventureWorksDW2008R2' SQL Server database to run. You can grab it from here: http://msftdbprodsamples.codeplex.com/", e); } using (var command = connection.CreateCommand()) { command.CommandText = sql; return command.ExecuteReader(CommandBehavior.CloseConnection); } }
public override bool Init(IDictionary<string, string> jobArgsDictionary) { try { PackageDatabase = new SqlConnectionStringBuilder( JobConfigurationManager.GetArgument(jobArgsDictionary, JobArgumentNames.PackageDatabase, EnvironmentVariableKeys.SqlGallery)); var storageGalleryCstr = Environment.GetEnvironmentVariable(EnvironmentVariableKeys.StorageGallery); if (string.IsNullOrEmpty(storageGalleryCstr)) { throw new ArgumentException("Environment variable for storage gallery is not defined"); } ContentAccount = CloudStorageAccount.Parse(storageGalleryCstr); return true; } catch (Exception ex) { Trace.TraceError(ex.ToString()); } return false; }
public async Task<string> Export() { var sourceConnBuilder = new SqlConnectionStringBuilder(_processorSettings.SourceConnectionString); string exportBlobUri = null; try { var ieHelper = new ImportExportHelper(_log) { EndPointUri = Helpers.GetEndpointUris()[_processorSettings.SourceAzureEndpointName.ParseEnum(Endpoint.None)], StorageKey = _importExportSettings.StorageKey, ServerName = sourceConnBuilder.DataSource.Replace("tcp:", string.Empty).Replace(",1433", string.Empty), DatabaseName = sourceConnBuilder.InitialCatalog, UserName = sourceConnBuilder.UserID, Password = sourceConnBuilder.Password }; exportBlobUri = string.Format("https://{0}.blob.core.windows.net/{1}/{2}-{3:yyyyMMddHHmmssff}.bacpac", _importExportSettings.StorageAccountName, _importExportSettings.StorageContainer, ieHelper.DatabaseName.ToLower(), DateTime.UtcNow); var exportBlobPath = ieHelper.DoExport(exportBlobUri); if (exportBlobPath == null) { _log.Error("Could not complete the export step for Backup and Restore of {SourceDatabase} to {ExportBlobPath}", _processorSettings.SourceConnectionString.ToSecretConnectionString(), exportBlobUri); throw new NullReferenceException("ImportExportHelper.DoExport()"); } return exportBlobPath; } catch (Exception ex) { _log.Error(ex, "Could not Backup {SourceDatabase} to {ExportBlobPath}", _processorSettings.SourceConnectionString.ToSecretConnectionString(), exportBlobUri ?? string.Empty); throw; } }
public string GetDatabaseName(string oldName, SqlConnectionStringBuilder connectionString, ref int postfix) { int postFix = postfix; string newName = oldName; while (true) { if (SqlServerManager.Instance.DatabaseExists(newName, connectionString)) { newName = oldName; postFix++; newName += "_" + postFix.ToString(); } else { if (postFix != -1) { postfix = postFix; } return newName; } } throw new Exception("Can't get a new DB name. (SIM.Pipelines.Import.ImportRestoreDatabases)"); }
// Bootstrap Elastic Scale by creating a new shard map manager and a shard map on // the shard map manager database if necessary. public Sharding(string smmserver, string smmdatabase, string smmconnstr) { // Connection string with administrative credentials for the root database SqlConnectionStringBuilder connStrBldr = new SqlConnectionStringBuilder(smmconnstr); connStrBldr.DataSource = smmserver; connStrBldr.InitialCatalog = smmdatabase; // Deploy shard map manager. ShardMapManager smm; if (!ShardMapManagerFactory.TryGetSqlShardMapManager(connStrBldr.ConnectionString, ShardMapManagerLoadPolicy.Lazy, out smm)) { this.ShardMapManager = ShardMapManagerFactory.CreateSqlShardMapManager(connStrBldr.ConnectionString); } else { this.ShardMapManager = smm; } ListShardMap<int> sm; if (!ShardMapManager.TryGetListShardMap<int>("ElasticScaleWithDapper", out sm)) { this.ShardMap = ShardMapManager.CreateListShardMap<int>("ElasticScaleWithDapper"); } else { this.ShardMap = sm; } }
public void CreateDatabase() { var connectoinStringBuilder = new SqlConnectionStringBuilder(ConnectionString); string dbName = connectoinStringBuilder.InitialCatalog; connectoinStringBuilder.InitialCatalog = string.Empty; using (var connection = new SqlConnection(connectoinStringBuilder.ToString())) { try { var serverConnection = new ServerConnection(connection); var server = new Microsoft.SqlServer.Management.Smo.Server(serverConnection); var db = new Database(server, dbName); db.Create(); } catch (Exception e) { throw new Exception(string.Format("Ошибка при создании БД - {0}", e)); } finally { if (connection.State == ConnectionState.Open) { connection.Close(); } } } }
/// <summary> /// Gets the context. /// </summary> /// <returns></returns> public static Apps4KidsEntities GetContext() { Apps4KidsEntities result; if (connectionString != null) { return new Apps4KidsEntities(connectionString); } EntityConnectionStringBuilder ecsb = new EntityConnectionStringBuilder(); SqlConnectionStringBuilder sqlcsb = new SqlConnectionStringBuilder(); ecsb.Metadata = @"res://*/DataModel.csdl| res://*/DataModel.ssdl| res://*/DataModel.msl"; ecsb.Provider = "System.Data.SqlClient"; sqlcsb.InitialCatalog = "Apps4Kids"; sqlcsb.DataSource = "localhost"; sqlcsb.UserID = "sa"; sqlcsb.Password = "******"; ecsb.ProviderConnectionString = sqlcsb.ToString(); result = new Apps4KidsEntities(ecsb.ConnectionString); return result; }
public MongoDbDriver(string connectionString, string collectionName) { var builder = new SqlConnectionStringBuilder(connectionString); _client = new MongoClient(builder.DataSource); _database = _client.GetDatabase(builder.InitialCatalog); _collectionName = collectionName; }
public static DalConfig ParseDalConfigXml(string dalConfigXml) { try { var ser = new DataContractSerializer(typeof (config.models.old.DalConfig)); var config = (config.models.old.DalConfig) ser.ReadObject(XmlReader.Create(new StringReader(dalConfigXml))); // now try to read and parse the the connection string var connString = ConfigurationManager.ConnectionStrings[config.ApplicationConnectionString]?.ConnectionString; var csb = new SqlConnectionStringBuilder(connString); return new DalConfig() { DesignerConnection = new DesignerConnection() { Authentication = ((config.DesignerConnection?.Authentication ?? new SimpleDataAccessLayer.Common.config.models.old.WindowsAuthentication()) is SimpleDataAccessLayer.Common.config.models.old.WindowsAuthentication) ? new config.models.Authentication { AuthenticationType = AuthenticationType.WindowsAuthentication} : new config.models.Authentication { AuthenticationType = AuthenticationType.SqlAuthentication, SavePassword = true, UserName = ((config.models.old.SqlAuthentication) config.DesignerConnection?.Authentication) ?.UserName, Password = ((config.models.old.SqlAuthentication) config.DesignerConnection?.Authentication) ?.Password }, DatabaseName = csb.InitialCatalog, ServerName = csb.DataSource }, RuntimeConnectionStringName = config.ApplicationConnectionString, Namespace = config.Namespace, Enums = config.Enums?.Select(e => new SimpleDataAccessLayer.Common.config.models.Enum() { Schema = e.Schema, Alias = e.Alias, ValueColumn = e.ValueColumn, KeyColumn = e.KeyColumn, TableName = e.TableName }).ToList(), Procedures = config.Procedures?.Select(p => new Procedure() { Alias = p.Alias, Schema = p.Schema, ProcedureName = p.ProcedureName }).ToList() }; } catch (Exception e) { throw new DalConfigXmlConverterException("Failed to parse DalConfig XML", e); } }
static void Main() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder["Data Source"] = "aad-managed-demo.database.windows.net"; // replace with your server name builder["Initial Catalog"] = "demo"; // replace with your database name builder["Authentication"] = SqlAuthenticationMethod.ActiveDirectoryPassword; builder["Connect Timeout"] = 30; string username = "******"; // replace with your username using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { try { connection.Credential = CreateCredential(username); connection.Open(); using (SqlCommand cmd = new SqlCommand(@"SELECT SUSER_SNAME()", connection)) { Console.WriteLine("You have successfully logged on as: " + (string)cmd.ExecuteScalar()); } } catch (Exception ex) { Console.WriteLine(ex.Message); } } Console.WriteLine("Please press any key to stop"); Console.ReadKey(); }
public Authorization_Form(SqlConnectionStringBuilder con, SelectServer_Form selectServer_Form) { InitializeComponent(); this.connectionString = con; this.selectServer_Form = selectServer_Form; Configuration(); }
protected string GetDatabaseSpecificConnectionString(Row row) { var builder = new SqlConnectionStringBuilder(_connectionString) { InitialCatalog = row["database"].ToString() }; return builder.ConnectionString; }
public void TestInitializer() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.DataSource = @".\SQLEXPRESS"; builder.IntegratedSecurity = true; builder.InitialCatalog = "DBBlogs"; SqlConnection connection1 = new SqlConnection(builder.ConnectionString); connection1.Open(); _currentTransaction1 = connection1.BeginTransaction(); SqlConnection connection2 = new SqlConnection(builder.ConnectionString); connection2.Open(); _currentTransaction2 = connection2.BeginTransaction(); MetaDataStore metaDataStore = new MetaDataStore(); metaDataStore.BuildTableInfoFor<Blog>(); var identityMap1 = new IdentityMap(); var identityMap2 = new IdentityMap(); _blogMapper1 = new BlogMapper(connection1, _currentTransaction1, metaDataStore, new EntityHydrater(metaDataStore, identityMap1), identityMap1); _blogMapper2 = new BlogMapper(connection2, _currentTransaction2, metaDataStore, new EntityHydrater(metaDataStore, identityMap2), identityMap2); }
public static void Initialize() { var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString; var connectionBuilder = new SqlConnectionStringBuilder(connectionString); //connect to postgres database to create a new database var databaseName = connectionBuilder.InitialCatalog; connectionBuilder.InitialCatalog = "master"; connectionString = connectionBuilder.ToString(); using (var conn = new SqlConnection(connectionString)) { conn.Open(); using (var cmd = new SqlCommand()) { cmd.CommandText = string.Format(@" IF db_id('{0}') IS NULL BEGIN CREATE DATABASE {0} END ", databaseName); cmd.Connection = conn; var result = cmd.ExecuteScalar(); } DropTables(conn, databaseName); } }
public override bool Init(IDictionary<string, string> jobArgsDictionary) { try { var instrumentationKey = JobConfigurationManager.TryGetArgument(jobArgsDictionary, JobArgumentNames.InstrumentationKey); ApplicationInsights.Initialize(instrumentationKey); var azureCdnPlatform = JobConfigurationManager.GetArgument(jobArgsDictionary, JobArgumentNames.AzureCdnPlatform); var cloudStorageAccountConnectionString = JobConfigurationManager.GetArgument(jobArgsDictionary, JobArgumentNames.AzureCdnCloudStorageAccount); var databaseConnectionString = JobConfigurationManager.GetArgument(jobArgsDictionary, JobArgumentNames.StatisticsDatabase); _cloudStorageAccount = ValidateAzureCloudStorageAccount(cloudStorageAccountConnectionString); _targetDatabase = new SqlConnectionStringBuilder(databaseConnectionString); _azureCdnAccountNumber = JobConfigurationManager.GetArgument(jobArgsDictionary, JobArgumentNames.AzureCdnAccountNumber); _azureCdnPlatform = ValidateAzureCdnPlatform(azureCdnPlatform); _cloudStorageContainerName = ValidateAzureContainerName(JobConfigurationManager.GetArgument(jobArgsDictionary, JobArgumentNames.AzureCdnCloudStorageContainerName)); return true; } catch (Exception exception) { ApplicationInsights.TrackException(exception); Trace.TraceError(exception.ToString()); } return false; }
/// <summary> /// Initializes parallel block during the call to <see cref="Parallel_Declare"/> stored procedure /// </summary> /// <param name="name"></param> public ParallelBlock(SqlString name) { string dbName = null; _startedThreads = new List<string>(); _name = name.IsNull ? string.Empty : name.Value; //let's get current server name SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(); connStrBuilder.ContextConnection = true; using (SqlConnection conn = new SqlConnection(connStrBuilder.ConnectionString)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT DB_NAME()"; cmd.CommandType = CommandType.Text; dbName = (string)cmd.ExecuteScalar(); } } //build connection string, which will be used to execute sql in threads connStrBuilder = new SqlConnectionStringBuilder(); connStrBuilder.DataSource = "(local)"; connStrBuilder.InitialCatalog = dbName; connStrBuilder.IntegratedSecurity = true; connStrBuilder.MultipleActiveResultSets = false; connStrBuilder.Pooling = true; connStrBuilder.MaxPoolSize = 30; connStrBuilder.ConnectTimeout = 30; //Enlisting will be done when connecting to server if transaction is enabled connStrBuilder.Enlist = false; _connectionString = connStrBuilder.ConnectionString; _threads = new List<ThreadSql>(); }
public static void PurgeDb(Seed seed) { ConnectionProfile cp = new ConnectionProfile(); SqlConnectionStringBuilder conn_orig = new SqlConnectionStringBuilder(cp.ConnectionString); SqlConnectionStringBuilder conn = new SqlConnectionStringBuilder(cp.ConnectionString) { ConnectTimeout = 5, InitialCatalog = "master" }; // you can add other parameters. using (SqlConnection cnn = new SqlConnection(conn.ConnectionString)) { cnn.Open(); using (SqlCommand dbCreate = new SqlCommand()) { dbCreate.CommandText = string.Format(@"IF db_id('{0}') IS NULL CREATE DATABASE [{0}]", conn_orig.InitialCatalog); dbCreate.Connection = cnn; try { dbCreate.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(string.Format("create database [{0}] FAILED with {1}", conn_orig.InitialCatalog, ex.Message )); } } } seed.PurgeDb(); }
public static void Backup(string savePath) { Directory.CreateDirectory(savePath); var windowsDrive = Path.GetPathRoot(Environment.SystemDirectory); var tempPath = Path.Combine(windowsDrive, "eugenie_temp"); Directory.CreateDirectory(tempPath); var connectionString = ConfigurationManager.ConnectionStrings["Eugenie"].ConnectionString; var sqlConStrBuilder = new SqlConnectionStringBuilder(connectionString); var backupFileName = Path.Combine(tempPath, $"{sqlConStrBuilder.InitialCatalog}-{DateTime.Now.ToString("yyyy-MM-dd-HH-mm")}.bak"); using (var connection = new SqlConnection(sqlConStrBuilder.ConnectionString)) { var query = $"BACKUP DATABASE {sqlConStrBuilder.InitialCatalog} TO DISK='{backupFileName}'"; using (var command = new SqlCommand(query, connection)) { connection.Open(); command.ExecuteNonQuery(); } } ZipFile.CreateFromDirectory(tempPath, Path.Combine(savePath, $"{sqlConStrBuilder.InitialCatalog}-{DateTime.Now.ToString("yyyy-MM-dd-HH-mm")}.zip"), CompressionLevel.Optimal, false); Directory.Delete(tempPath, true); }
public static DatabaseModel FromConnectionString(string ConnectionString) { var result = new DatabaseModel(); try { var csb = new SqlConnectionStringBuilder(ConnectionString); if (!string.IsNullOrEmpty(csb.DataSource)) result.Server = csb.DataSource; if (!string.IsNullOrEmpty(csb.InitialCatalog)) result.DatabaseName = csb.InitialCatalog; if (csb.IntegratedSecurity) { result.AuthMethod = "SSPI"; } else { result.AuthMethod = "SQL"; result.Auth_SQL_Username = csb.UserID; result.Auth_SQL_Password = csb.Password; } } catch (Exception) { // Ignore Parsing errors } return result; }
public SqlServerMetadataReader(string connectionStr) { this.connectionStr = connectionStr; SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder(connectionStr); DatabaseName = connStr.InitialCatalog; }
/// <summary> /// Parses a SQL Server connection URI in the format /// mssql://username:password@server[/instance]/database /// </summary> /// <param name="uri"></param> /// <returns></returns> public static string Parse(string uri) { var u = new Uri(uri); if (u.Scheme.ToLower().NotIn("mssql", "mssqls")) throw new ArgumentException(string.Format("{0} is not a valid protocol", u.Scheme)); var csb = new SqlConnectionStringBuilder(); csb.DataSource = u.Host; csb.Encrypt = u.Scheme.ToLower() == "mssqls"; var pathParts = u.AbsolutePath.Split('/'); if (pathParts.Length == 3) { csb.DataSource += "\\" + Uri.UnescapeDataString(pathParts[1]); csb.InitialCatalog = Uri.UnescapeDataString(pathParts[2]); } if (pathParts.Length == 2) csb.InitialCatalog = Uri.UnescapeDataString(pathParts[1]); if (u.UserInfo.IsBlank()) csb.IntegratedSecurity = true; else { var userParts = u.UserInfo.Split(new[] {':'}, 2); csb.UserID = Uri.UnescapeDataString(userParts[0]); if (userParts.Length == 2) csb.Password = Uri.UnescapeDataString(userParts[1]); } return csb.ConnectionString; }
public override bool CheckConnectionString(string connectionString) { try { var builder = new SqlConnectionStringBuilder(connectionString); if (File.Exists(builder.DataSource) == false) { var containingFolder = builder.DataSource.Substring(0, builder.DataSource.LastIndexOf("\\")); if (Directory.Exists(containingFolder) == false) { GenericUtils.CreateFolderStructure(containingFolder); } var engine = new SqlCeEngine(connectionString); engine.CreateDatabase(); } } catch (Exception ex) { Console.WriteLine("Unable to create SQL CE database automatically. The database should be created manually. Error Details : " + ex.Message); } var connectionCretead = CreateConnection(null, connectionString); if (connectionCretead != null && connectionCretead.State == System.Data.ConnectionState.Open) { connectionCretead.Close(); return true; } return false; }
/// <summary> /// </summary> /// <returns></returns> private SqlConnectionStringBuilder BuildConnectionString() { var builder = new SqlConnectionStringBuilder(); cbAuthentication.Invoke(() => { builder.IntegratedSecurity = cbAuthentication.SelectedIndex == 0; if (builder.IntegratedSecurity) { return; } builder.UserID = cbLogin.Text; builder.Password = txtPassword.Text; }); cbDatabase.Invoke(() => { if (cbDatabase.Text.IsEmpty()) { return; } builder.InitialCatalog = cbDatabase.Text; }); cbServerName.Invoke(() => { builder.DataSource = cbServerName.Text; }); return builder; }
static void Main(string[] args) { SqlConnectionStringBuilder strConn = new SqlConnectionStringBuilder(); strConn.DataSource = "DESKTOP-THJOVJ1"; strConn.InitialCatalog = "Test"; strConn.IntegratedSecurity = true; using (SqlConnection conn = new SqlConnection(strConn.ConnectionString)) { string strSQL = "select * from [User]"; SqlCommand cmd = new SqlCommand(strSQL,conn); conn.Open(); try { SqlDataReader reader = cmd.ExecuteReader(); if (reader != null && reader.HasRows) { int rows = 0; } } catch (Exception ex) { } } }
public bool TestServerConnectionWithoutDatabase() { if (DBType.Equals(WebUtils.DBTypeEmun.MSSQL)) { System.Data.SqlClient.SqlConnection testConn = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlConnectionStringBuilder connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(ConnectionString); connStringBuilder.InitialCatalog = ""; testConn.ConnectionString = connStringBuilder.ConnectionString; try { testConn.Open(); testConn.Close(); } catch { return(false); } return(true); } else { return(false); } }
private string GetDbName() { System.Data.SqlClient.SqlConnectionStringBuilder lObjBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(GetConnection()); return(lObjBuilder["Initial Catalog"] as string); }
static void Main(string[] args) { using (SqlConnection connString = new SqlConnection()) { //Create the connection to the database and open the connection System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); Connection(); void Connection() { string instance = null; string dbname = null; string username = null; string password = null; Console.WriteLine("Enter your SQL instance."); instance = Console.ReadLine(); Console.WriteLine("Enter your Database name"); dbname = Console.ReadLine(); Console.WriteLine("Enter your SQL Username"); username = Console.ReadLine(); Console.WriteLine("Enter your Password"); password = Console.ReadLine(); builder["Data Source"] = instance; builder["Integrated Security"] = false; builder["Initial Catalog"] = dbname; builder["User ID"] = username; builder["Password"] = password; Console.WriteLine(builder.ConnectionString); connString.ConnectionString = builder.ConnectionString; try { connString.Open(); Console.WriteLine("You're connected to the SPS database. \nEnter your query."); Query(); } catch (SqlException) { Console.WriteLine("Unable to connect, please try again."); Connection(); } } void Query() { ConnectionState dbstate = connString.State; while (dbstate == ConnectionState.Open) { Console.WriteLine(""); string query = Console.ReadLine(); SqlCommand command = new SqlCommand(query, connString); //SqlCommand command = new SqlCommand("SELECT * FROM Users", connString); using (SqlDataReader reader = command.ExecuteReader()) { //Console.WriteLine("Genre ID | \t Name"); while (reader.Read()) { Console.WriteLine(string.Format("{0, -15}| {1, -15}", reader[0], reader[1])); } } } Console.WriteLine("Data displayed! Now press enter to move to the next section!"); } } }
/// <summary> /// /// </summary> /// <param name="emailAddress">Email address to send mail to when responding to an event.</param> /// <param name="csb">Connection to SQL for [msdb].[dbo].[sp_send_dbmail]</param> public ResponseOptions(string emailAddress, System.Data.SqlClient.SqlConnectionStringBuilder csb) { InitializeClass(null, emailAddress, csb); }
protected void GetDataBaseSchema(string ConnectionString) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder.ConnectionString = ConnectionString; string server = builder.DataSource; string database = builder.InitialCatalog; connection.Open(); DataTable schemaTables = connection.GetSchema("Tables"); foreach (System.Data.DataRow rowTable in schemaTables.Rows) { // index 1 is the schema name and index 2 is the tablename String tableName = rowTable.ItemArray[2].ToString(); //this.TableList.Add(tableName); string schemaName = rowTable.ItemArray[1].ToString(); Tables table = new DatabaseManager.Tables() { TableName = tableName, SchemaName = schemaName }; this.TableList.Add(table); string[] restrictionsColumns = new string[4]; restrictionsColumns[2] = tableName; DataTable schemaColumns = connection.GetSchema("Columns", restrictionsColumns); foreach (System.Data.DataRow rowColumn in schemaColumns.Rows) { string ColumnName = rowColumn[3].ToString(); string columnType = rowColumn[7].ToString(); this.ColumnList.Add(new Columns() { TableName = tableName, FieldName = ColumnName, ColumnType = columnType }); } string[] restrictionsPrimaryKey = new string[4]; restrictionsPrimaryKey[2] = tableName; DataTable schemaPrimaryKey = connection.GetSchema("IndexColumns", restrictionsColumns); foreach (System.Data.DataRow rowPrimaryKey in schemaPrimaryKey.Rows) { string indexName = rowPrimaryKey[2].ToString(); if (indexName.IndexOf("PK_") != -1) { //for (int count = 0; count < 10; count++) //{ // Console.WriteLine($"Count: {count} {rowPrimaryKey[count]}"); // Console.WriteLine(); //} //if (tableName == "PersonPhone") //{ // Console.WriteLine(rowPrimaryKey[6]); // Console.WriteLine(indexName); //} this.PrimaryKeyList.Add(new PrimaryKey() { TableName = tableName, FieldName = rowPrimaryKey[6].ToString(), PrimaryKeyName = indexName }); } if (indexName.IndexOf("UQ_") != -1) { this.UniqueKeyList.Add(new UniqueKey() { TableName = tableName, FieldName = rowPrimaryKey[6].ToString(), UniqueKeyName = indexName }); } } string[] restrictionsForeignKeys = new string[4]; restrictionsForeignKeys[2] = tableName; DataTable schemaForeignKeys = connection.GetSchema("ForeignKeys", restrictionsColumns); foreach (System.Data.DataRow rowFK in schemaForeignKeys.Rows) { this.ForeignKeyList.Add(new ForeignKey() { ForeignName = rowFK[2].ToString(), TableName = tableName, // FieldName = rowFK[6].ToString() //There is no information }); } } } }
public static string GetConnection(string serverName, string catalog, bool?integratedSecurity, string username, string password, string connectionStringExample, SqlProduct sqlProduct, int localPort, bool usesSsh) { string connectionString = null; System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder.ConnectionString = connectionStringExample; bool hasServer = !string.IsNullOrEmpty(serverName); bool hasCatalog = !string.IsNullOrEmpty(catalog); if (!hasCatalog) { throw new DuradosException("Catalog Name is missing"); } if (integratedSecurity.HasValue && integratedSecurity.Value) { if (!hasServer) { serverName = builder.DataSource; } connectionString = "Data Source={0};Initial Catalog={1};Integrated Security=True;"; return(string.Format(connectionString, serverName, catalog)); } else { connectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3};Integrated Security=False;"; if (sqlProduct == SqlProduct.MySql) { if (usesSsh) { serverName = "localhost"; } connectionString = "server={0};database={1};User Id={2};password={3};port={4};convert zero datetime=True"; } bool hasUsername = !string.IsNullOrEmpty(username); bool hasPassword = !string.IsNullOrEmpty(password); if (!hasServer) { serverName = builder.DataSource; } if (!hasUsername) { username = builder.UserID; } if (!hasPassword) { password = builder.Password; } return(string.Format(connectionString, serverName, catalog, username, password, localPort)); } }
public DatabaseConfig GenerateDBType() { DatabaseConfig config = new DatabaseConfig(); if (string.IsNullOrEmpty(MSSQLDatabase.Text)) { throw new Exception("Database must be fill in."); //return null; } config.DBType = WebUtils.DBTypeEmun.MSSQL; System.Data.SqlClient.SqlConnectionStringBuilder connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(); connStringBuilder.DataSource = MSSQLServerLocation.Text; connStringBuilder.InitialCatalog = MSSQLDatabase.Text; connStringBuilder.UserID = MSSQLUserID.Text; connStringBuilder.Password = MSSQLPassword.Text; config.ConnectionString = connStringBuilder.ConnectionString; if (config.TestConnection()) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStringBuilder.ConnectionString); conn.Open(); DataTable table = conn.GetSchema("Tables"); conn.Close(); DataRow[] rows = table.Select("TABLE_NAME='SystemParameter'"); if (rows.GetLength(0) == 0) if (chkCreateDatabase.Checked) CreateSchema(config.ConnectionString); else throw new Exception("Database does not contain table"); return config; } else { if (config.TestServerConnectionWithoutDatabase()) { if (chkCreateDatabase.Checked) { try { string saUser = SAUserID.Text; string saPassword = SAPassword.Text; if (saUser.Trim() == string.Empty) { saUser = MSSQLUserID.Text; saPassword = MSSQLPassword.Text; } connStringBuilder.InitialCatalog = string.Empty; connStringBuilder.UserID = saUser; connStringBuilder.Password = saPassword; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStringBuilder.ConnectionString); System.Data.SqlClient.SqlCommand command = conn.CreateCommand(); command.CommandType = System.Data.CommandType.Text; command.CommandText = "Create Database " + MSSQLDatabase.Text + "\r\n"; command.Connection.Open(); command.ExecuteNonQuery(); if (MSSQLUserID.Text.Trim() != saUser.Trim()) { command.CommandText = "USE " + MSSQLDatabase.Text + "\r\n" + "CREATE USER " + MSSQLUserID.Text + " FOR LOGIN " + MSSQLUserID.Text + "\r\n" + "EXEC sp_addrolemember N'db_owner', N'" + MSSQLUserID.Text + "'"; command.ExecuteNonQuery(); } command.Connection.Close(); connStringBuilder.InitialCatalog = MSSQLDatabase.Text; CreateSchema(connStringBuilder.ConnectionString); return config; } catch (Exception ex) { throw new Exception("Error on creating Database:\n" + ex.Message); } } else throw new Exception("Invalid Database name."); } else { throw new Exception("Fail to connect to server."); } } //return null; }
static void Main(string[] args) { Console.WriteLine("Введите адрес сервера"); string hostname = Console.ReadLine(); Console.WriteLine("Введите имя базы данных на сервере MS SQL : {0}", hostname); string database = Console.ReadLine(); Console.WriteLine("Введите пользователя MS SQL к {0}", database); string login = Console.ReadLine(); Console.WriteLine("Введите пароль пользователя MS SQL : {0}", login); string password = Console.ReadLine(); System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder["Data Source"] = hostname; builder["integrated Security"] = false; builder["Initial Catalog"] = database; builder["user id"] = login; builder["Password"] = password; Console.WriteLine(builder.ConnectionString); SqlConnection conn = new SqlConnection(builder.ConnectionString); try { Console.WriteLine("Start connection"); conn.Open(); Console.WriteLine("Connection ready"); // get last userid string getUserId = String.Format("SELECT MAX(ID) FROM pList"); SqlCommand Idcommand = new SqlCommand(getUserId, conn); int userid = Convert.ToInt32(Idcommand.ExecuteScalar()); // get last tabnumber string getTabNumber = String.Format("SELECT MAX(ID) FROM pList"); SqlCommand TabNumbercommand = new SqlCommand(getTabNumber, conn); int tabnumber = Convert.ToInt32(TabNumbercommand.ExecuteScalar()); int status = 8; string name_adder = "_"; string Name = ""; string StaticName = "Шаблонов"; string FirstName = "Шаблон"; string MidName = "Шаблонович"; int pattern = 1; // circle for (int i = 0; i < 30000; i++) { userid++; tabnumber++; if (i > 0) { Name = StaticName + name_adder + userid; } else { Name = userid > 1 ? StaticName + name_adder + userid : StaticName; } // do some queries string sqlExpression = String.Format("INSERT INTO pList (ID, Name, FirstName, Status, MidName, TabNumber, GrStatus) VALUES ({0}, '{1}', '{2}', {3}, '{4}', {5}, {6})", userid, Name, FirstName, status, MidName, tabnumber, pattern); SqlCommand command = new SqlCommand(sqlExpression, conn); command.ExecuteNonQuery(); Console.WriteLine("Добавлен: {0}, '{1}', '{2}', '{3}', {4}, {5}", userid, Name, FirstName, MidName, tabnumber, pattern); } Console.WriteLine("Circle ended"); // end connections conn.Close(); Console.WriteLine("Connection ended"); } catch (Exception e) { Console.WriteLine(e.ToString()); } }
/// <summary> /// Determine the connection string for the specified <see cref="SqlRequest"/>. /// </summary> /// <param name="request"> /// The <see cref="SqlRequest"/> being executed. /// </param> /// <returns> /// The connection string. /// </returns> async Task <string> GetConnectionString(SqlRequest request) { if (request == null) { throw new ArgumentNullException(nameof(request)); } Log.LogInformation("Determining connection string for database {DatabaseId} in server {ServerId}...", request.DatabaseId, request.ServerId ); DatabaseServer targetServer = await DocumentSession.LoadAsync <DatabaseServer>(request.ServerId); if (targetServer == null) { Log.LogWarning("Cannot determine connection string for database {DatabaseId} in server {ServerId} (server not found).", request.DatabaseId, request.ServerId ); throw RespondWith(Ok(new SqlResult { ResultCode = -1, Errors = { new SqlError { Kind = SqlErrorKind.Infrastructure, Message = $"Unable to determine connection settings for database {request.DatabaseId} in server {request.ServerId} (server not found)." } } })); } List <ServiceV1> matchingServices = await KubeClient.ServicesV1().List( labelSelector: $"cloud.dimensiondata.daas.server-id = {targetServer.Id},cloud.dimensiondata.daas.service-type = internal", kubeNamespace: KubeOptions.KubeNamespace ); if (matchingServices.Count == 0) { Log.LogWarning("Cannot determine connection string for database {DatabaseId} in server {ServerId} (server's associated Kubernetes Service not found).", request.DatabaseId, request.ServerId ); throw RespondWith(Ok(new SqlResult { ResultCode = -1, Errors = { new SqlError { Kind = SqlErrorKind.Infrastructure, Message = $"Unable to determine connection settings for database {request.DatabaseId} in server {request.ServerId} (server's associated Kubernetes Service not found)." } } })); } ServiceV1 serverService = matchingServices[matchingServices.Count - 1]; (string serverFQDN, int?serverPort) = serverService.GetHostAndPort(portName: "sql-server"); if (serverPort == null) { Log.LogWarning("Cannot determine connection string for database {DatabaseId} in server {ServerId} (cannot find the port named 'sql-server' on server's associated Kubernetes Service).", request.DatabaseId, request.ServerId ); throw RespondWith(Ok(new SqlResult { ResultCode = -1, Errors = { new SqlError { Kind = SqlErrorKind.Infrastructure, Message = $"Unable to determine connection settings for database {request.DatabaseId} in server {request.ServerId} (cannot find the port named 'sql-server' on server's associated Kubernetes Service)." } } })); } Log.LogInformation("Database proxy will connect to SQL Server '{ServerFQDN}' on {ServerPort}.", serverFQDN, serverPort); var connectionStringBuilder = new SqlClient.SqlConnectionStringBuilder { DataSource = $"tcp:{serverFQDN},{serverPort}", }; var serverSettings = targetServer.GetSettings <SqlServerSettings>(); if (request.DatabaseId != MasterDatabaseId) { DatabaseInstance targetDatabase = await DocumentSession.LoadAsync <DatabaseInstance>(request.DatabaseId); if (targetDatabase == null) { Log.LogWarning("Cannot determine connection string for database {DatabaseId} in server {ServerId} (database not found).", request.DatabaseId, request.ServerId ); throw RespondWith(Ok(new SqlResult { ResultCode = -1, Errors = { new SqlError { Kind = SqlErrorKind.Infrastructure, Message = $"Unable to determine connection settings for database {request.DatabaseId} in server {request.ServerId} (database not found)." } } })); } connectionStringBuilder.InitialCatalog = targetDatabase.Name; if (request.ExecuteAsAdminUser) { connectionStringBuilder.UserID = "sa"; connectionStringBuilder.Password = serverSettings.AdminPassword; } else { connectionStringBuilder.UserID = targetDatabase.DatabaseUser; connectionStringBuilder.Password = targetDatabase.DatabasePassword; } } else { connectionStringBuilder.InitialCatalog = "master"; connectionStringBuilder.UserID = "sa"; connectionStringBuilder.Password = serverSettings.AdminPassword; } Log.LogInformation("Successfully determined connection string for database {DatabaseId} ({DatabaseName}) in server {ServerId} ({ServerSqlName}).", request.DatabaseId, connectionStringBuilder.InitialCatalog, request.ServerId, connectionStringBuilder.DataSource ); return(connectionStringBuilder.ConnectionString); }
public static void Main() { var ConnectionString = @"Data Source=HAKTANENESB8201\SQLEXPRESS;Initial Catalog=SqlSample;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(ConnectionString); var cnn = new SqlConnection(builder.ConnectionString); var userStore = new UserStore(cnn); var houseStore = new HouseStore(cnn); var user = new User { FullName = "Nasut Evren Kayali", Email = "*****@*****.**", Phone = "3234", TC = 1 }; var user2 = new User { FullName = "Haktan Enes Biçer", Email = "*****@*****.**", Phone = "CXZ", TC = 2 }; var user3 = new User { FullName = "asfasf", Email = "easfasfnet", Phone = "3234", TC = 3 }; var house = new House { Address = "AAAAAAAA", Owner = new User { FullName = "Nasut Evren Kayali", Email = "*****@*****.**", } }; var house2 = new House { Address = "BBBBBB", Owner = new User { FullName = "Deneme", Email = "*****@*****.**", TC = 3 } }; // userStore.Save(user); // userStore.Save(user2); //userStore.Save(user3); //houseStore.Save(house); // houseStore.Save(house2); // var firstUser = userStore.GetByTc(1); // Console.WriteLine(firstUser); Console.WriteLine("******************"); var firstHouse = houseStore.getById(3); Console.WriteLine(firstHouse); Console.WriteLine("******************"); //var allUsers = userStore.GetAll(); //foreach (var u in allUsers) Console.WriteLine(u); var allHousesNotOwnedHouses = houseStore.GetAll(); foreach (var u in allHousesNotOwnedHouses) { Console.WriteLine(u); } var allHousesOwnedHouses = houseStore.GetAllOwnedHouses(); foreach (var u in allHousesOwnedHouses) { Console.WriteLine(u); } }
public FileResult DownloadReport(string ReportName, string ReportFormat) { // Verify ReportFormat is passed if (!ReportFormats.ContainsKey(ReportFormat)) { ArgumentException ex = new ArgumentException(string.Format(@"Unrecognized report format ""?"".", ReportFormat)); throw ex; } // If DSR report, see if needs to be resegmented before reporting if (ReportName == "Dynamic Segmentation Records") { //Resegment(); //PH 2014.05.22 need user id //PipelineFeatureList.AppCode.AppLibrary.CopyToHistoryGenerateGradesDynamicSegmentation(Convert.ToInt64(Session["CurrentValveSection"].ToString()), 0, 0, 1); PipelineFeatureList.AppCode.AppLibrary.CopyToHistoryGenerateGradesDynamicSegmentation( Convert.ToInt64(Session["UserID"].ToString()), Convert.ToInt64(Session["CurrentValveSection"].ToString()), 0, 0, 1); //PH 2014.05.22 end edit Thread.Sleep(10000); } // Get connected database var sqlDB = new System.Data.SqlClient.SqlConnectionStringBuilder( System.Configuration.ConfigurationManager.ConnectionStrings["PipelineFeatureListDBContext"].ConnectionString); var dbName = sqlDB.InitialCatalog; var dbDataSource = sqlDB.DataSource; // Set report path ReportViewer rptViewer = new ReportViewer(); string urlReportServer = "http://" + dbDataSource + "/ReportServer"; // "http://g2dev.g2partnersllc.local/ReportServer"; rptViewer.ProcessingMode = ProcessingMode.Remote; // ProcessingMode will be Either Remote or Local rptViewer.ServerReport.ReportServerUrl = new Uri(urlReportServer); //Set the ReportServer Url rptViewer.ServerReport.ReportPath = "/" + dbName + "/" + ReportName; //Passing the Report Path //Creating an ArrayList for combine the Parameters which will be passed into SSRS Report ArrayList reportParam = new ArrayList(); reportParam = ReportDefaultParam(); ReportParameter[] param = new ReportParameter[reportParam.Count]; for (int k = 0; k < reportParam.Count; k++) { param[k] = (ReportParameter)reportParam[k]; } // pass crendentials IReportServerCredentials irsc = new CustomReportCredentials(); rptViewer.ServerReport.ReportServerCredentials = irsc; //pass parmeters to report rptViewer.ServerReport.SetParameters(param); //Set Report Parameters rptViewer.ServerReport.Refresh(); byte[] streamBytes = null; string mimeType = ""; string encoding = ""; string filenameExtension = ""; string[] streamids = null; Warning[] warnings = null; string filename = ReportName + "." + ReportFormats[ReportFormat]; streamBytes = rptViewer.ServerReport.Render(ReportFormat, null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings); return(File(streamBytes, mimeType, filename)); }
protected void Save_Click(object sender, EventArgs e) { HROneSaaSConfig SaaSconfig = HROneSaaSConfig.GetCurrentConfig(); string HROnePath = new System.IO.FileInfo(SaaSconfig.HROneConfigFullPath).Directory.FullName; ECompanyDatabase c = new ECompanyDatabase(); Hashtable values = new Hashtable(); binding.toValues(values); PageErrors errors = PageErrors.getErrors(db, Page.Master); errors.clear(); db.validate(errors, values); if (!errors.isEmpty()) { return; } db.parse(values, c); if (!chkAutoCreateID.Checked && string.IsNullOrEmpty(c.CompanyDBClientCode)) { errors.addError("Client ID is required"); return; } HROne.ProductKey key = new HROne.ProductKey(); key.ProductType = HROne.ProductKey.ProductLicenseType.HROneSaaS; key.NumOfCompanies = Convert.ToUInt16(c.CompanyDBMaxCompany); key.NumOfUsers = Convert.ToUInt16(c.CompanyDBMaxUser); if (c.CompanyDBHasIMGR) { key.IsLeaveManagement = true; key.IsPayroll = true; key.IsTaxation = true; } if (c.CompanyDBHasIStaff) { key.IsESS = true; } if (string.IsNullOrEmpty(c.CompanyDBClientCode)) { const int MAX_LENGTH = 8; string prefix = CreateClientCodePrefix(c.CompanyDBClientName); //if (c.CompanyDBClientBank.Equals("HSBC", StringComparison.CurrentCultureIgnoreCase)) // prefix = "H"; //else if (c.CompanyDBClientBank.Equals("HangSeng", StringComparison.CurrentCultureIgnoreCase)) // prefix = "X"; int idx = 0; if (prefix.Length >= MAX_LENGTH) { prefix = prefix.Substring(0, MAX_LENGTH); } else { idx++; string idxString = idx.ToString().Trim(); prefix = prefix.PadRight(MAX_LENGTH - idxString.Length, '0') + idxString; } c.CompanyDBClientCode = prefix; while (!AppUtils.checkDuplicate(dbConn, ECompanyDatabase.db, c, new PageErrors(), "CompanyDBClientCode")) { idx++; string idxString = idx.ToString().Trim(); c.CompanyDBClientCode = prefix.Substring(0, MAX_LENGTH - idxString.Length) + idxString; } } if (!AppUtils.checkDuplicate(dbConn, ECompanyDatabase.db, c, errors, "CompanyDBClientCode")) { return; } EDatabaseServer dbServer = new EDatabaseServer(); dbServer.DBServerID = c.DBServerID; if (EDatabaseServer.db.select(dbConn, dbServer)) { if (dbServer.DBServerDBType.Equals("MSSQL")) { System.Data.SqlClient.SqlConnectionStringBuilder saConnStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(); saConnStringBuilder.DataSource = dbServer.DBServerLocation; saConnStringBuilder.UserID = dbServer.DBServerSAUserID; saConnStringBuilder.Password = dbServer.DBServerSAPassword; DatabaseConfig dbConfig = new DatabaseConfig(); dbConfig.DBType = WebUtils.DBTypeEmun.MSSQL; dbConfig.ConnectionString = saConnStringBuilder.ConnectionString; if (dbConfig.TestServerConnectionWithoutDatabase()) { string DBSchemaName = c.CompanyDBSchemaName.Trim(); if (DBSchemaName.Equals(string.Empty)) { DBSchemaName = c.CompanyDBClientCode; } System.Data.SqlClient.SqlConnectionStringBuilder connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(); connStringBuilder.DataSource = dbServer.DBServerLocation; connStringBuilder.InitialCatalog = DBSchemaName; connStringBuilder.UserID = dbServer.DBServerUserID; connStringBuilder.Password = dbServer.DBServerPassword; dbConfig.ConnectionString = connStringBuilder.ConnectionString; if (!dbConfig.TestConnection()) { if (chkCreateDB.Checked) { try { HROne.ProductVersion.Database.CreateSchema(saConnStringBuilder.ConnectionString, DBSchemaName, dbServer.DBServerUserID); //c.CompanyDBSchemaName = DBSchemaName; saConnStringBuilder.InitialCatalog = DBSchemaName; HROne.ProductVersion.Database.CreateTableAndData(HROnePath, saConnStringBuilder.ConnectionString); // drop all the connection so that new "normal user" connection to database is accepted System.Data.SqlClient.SqlConnection.ClearAllPools(); } catch (Exception ex) { errors.addError(ex.Message); return; } } else { errors.addError("Fail to connect to database"); return; } } } else { errors.addError("Fail to connect to server"); return; } } } if (CurID < 0) { // Utils.MarkCreate(Session, c); db.insert(dbConn, c); CurID = c.CompanyDBID; HSBCExchangeProfile_List1.CompanyDBID = CurID; // url = Utils.BuildURL(-1, CurID); } else { // Utils.Mark(Session, c); } key.SerialNo = Convert.ToUInt16(c.CompanyDBID); c.CompanyDBProductKey = key.GetProductKey(); db.update(dbConn, c); HROne.ProductVersion.Database databaseProcess = new HROne.ProductVersion.Database(new DatabaseConnection(c.getConnectionString(dbConn), DatabaseConnection.DatabaseType.MSSQL), HROnePath); databaseProcess.UpdateDatabaseVersion(true); errors.addError("Saved"); loadObject(); //Response.Redirect("~/CompanyDB_List.aspx"); }
private void DbYoluYaz(string ServerNameAres, string DbNameAres, string ConStrNameAres, string UserNameAres, string PasswordAres, string ServerNameAresUT, string DbNameAresUT, string ConStrNameAresUT, string UserNameAresUT, string PasswordAresUT) { if (ServerNameAres == "" || DbNameAres == "" || ServerNameAresUT == "" || DbNameAresUT == "") { return; } string strPath = Application.StartupPath + @"\AresUrunTanitim.exe.config"; char tirnak = '"'; System.Data.SqlClient.SqlConnectionStringBuilder ConnBuilderAres = new System.Data.SqlClient.SqlConnectionStringBuilder(); System.Data.SqlClient.SqlConnectionStringBuilder ConnBuilderAresUT = new System.Data.SqlClient.SqlConnectionStringBuilder(); ConnBuilderAres.DataSource = ServerNameAres; ConnBuilderAres.InitialCatalog = DbNameAres; if (UserNameAres == "" && PasswordAres == "") { ConnBuilderAres.IntegratedSecurity = true; } else if (UserNameAres != "" && PasswordAres != "") { ConnBuilderAres.UserID = UserNameAres; ConnBuilderAres.Password = PasswordAres; ConnBuilderAres.PersistSecurityInfo = true; } else if (UserNameAres != "" && PasswordAres == "") { ConnBuilderAres.UserID = UserNameAres; } ConnBuilderAresUT.DataSource = ServerNameAresUT; ConnBuilderAresUT.InitialCatalog = DbNameAresUT; if (UserNameAresUT == "" && PasswordAresUT == "") { ConnBuilderAresUT.IntegratedSecurity = true; } else if (UserNameAresUT != "" && PasswordAresUT != "") { ConnBuilderAresUT.UserID = UserNameAresUT; ConnBuilderAresUT.Password = PasswordAresUT; ConnBuilderAresUT.PersistSecurityInfo = true; } else if (UserNameAresUT != "" && PasswordAresUT == "") { ConnBuilderAresUT.UserID = UserNameAresUT; } string strText = "<?xml version=" + tirnak + "1.0" + tirnak + " encoding=" + tirnak + "utf-8" + tirnak + " ?>" + "<configuration>" + "<configSections>" + "</configSections>" + "<connectionStrings>" + "<add name=" + tirnak + "AresUrunTanitim.Properties.Settings.DBConStrAres" + tirnak + " connectionString=" + tirnak + ConnBuilderAres.ConnectionString + tirnak + " providerName=" + tirnak + "System.Data.SqlClient" + tirnak + " />" + "<add name=" + tirnak + "AresUrunTanitim.Properties.Settings.DBConStrAresUT" + tirnak + " connectionString=" + tirnak + ConnBuilderAresUT.ConnectionString + tirnak + " providerName=" + tirnak + "System.Data.SqlClient" + tirnak + " />" + "</connectionStrings>" + "</configuration>"; try { File.Delete(strPath); File.WriteAllText(strPath, strText, Encoding.UTF8); XtraMessageBox.Show("VERİTABANI DEĞİŞİKLİKLERİ KAYDEDİLDİ.", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception hata) { XtraMessageBox.Show("Hata Kodu : " + hata.Message + "\nHata Açıklama : " + hata.StackTrace, " ", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
static void Main(string[] args) { try { System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder.DataSource = "AKSHANSH-IN08"; builder.UserID = "Akshansh"; builder.Password = "******"; builder.InitialCatalog = "master"; Console.Write("conneting to db"); using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { connection.Open(); Console.WriteLine(); Console.WriteLine("done"); Console.WriteLine(); Console.Write("Creating the Database SampleDb"); string sql = "DROP DATABASE IF EXISTS [SampleDB]; CREATE DATABASE [SampleDB]"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.ExecuteNonQuery(); Console.WriteLine(); Console.WriteLine("Done creating Database"); } Console.WriteLine(); Console.Write("Creating sample table with data, press any key to continue..."); Console.ReadKey(true); StringBuilder sb = new StringBuilder(); sb.Append("USE SampleDB; "); sb.Append("CREATE TABLE Employee(ID int primary key not null,NAME varchar(500),COUNTRY varchar(500));"); //creating table sb.Append("INSERT INTO Employee values "); //insert data sb.Append("(1,'Abhay','India'),"); sb.Append("(2,'Aman','India'),"); sb.Append("(3,'Jack','USA');"); sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { command.ExecuteNonQuery(); Console.WriteLine(); Console.WriteLine("Done creating Table and Inserting Values"); } show(connection); Console.Write("Inserting a new row into table, press any key to continue..."); Console.ReadKey(true); Console.WriteLine(); Console.WriteLine("Enter id"); int id = Int32.Parse(Console.ReadLine()); Console.WriteLine("Enter Name"); string name = Console.ReadLine(); Console.WriteLine("Enter Country"); string country = Console.ReadLine(); sb.Clear(); sb.Append("INSERT Employee "); sb.Append("VALUES (@id,@name, @location);"); sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { command.Parameters.AddWithValue("@id", id); command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@location", country); int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine(); Console.WriteLine(rowsAffected + " row(s) inserted"); Console.WriteLine(); } show(connection); Console.Write("Updating 'Country' for user , press any key to continue..."); Console.ReadKey(true); Console.WriteLine(); Console.WriteLine("Enter the name"); string userToUpdate = Console.ReadLine(); Console.WriteLine("Enter the Country"); string userToUpdate_Country = Console.ReadLine(); sb.Clear(); sb.Append("UPDATE Employee SET COUNTRY = @country WHERE NAME = @name"); sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { command.Parameters.AddWithValue("@country", userToUpdate_Country); command.Parameters.AddWithValue("@name", userToUpdate); int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine(rowsAffected + " row(s) updated"); Console.WriteLine(); } show(connection); Console.Write("Deleting user , press any key to continue..."); Console.ReadKey(true); Console.WriteLine(); Console.WriteLine("Enter the name"); string userToDelete = Console.ReadLine(); sb.Clear(); sb.Append("DELETE from Employee WHERE NAME = @name"); sql = sb.ToString(); using (SqlCommand command = new SqlCommand(sql, connection)) { command.Parameters.AddWithValue("@name", userToUpdate); int rowsAffected = command.ExecuteNonQuery(); Console.WriteLine(rowsAffected + " row(s) updated"); Console.WriteLine(); } show(connection); connection.Close(); } } catch (Exception e) { Console.WriteLine(e.ToString()); } Console.ReadKey(); }
public async Task <InvokeResult> ValidateConnectionAsync(DataStream stream) { var result = new InvokeResult(); var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder.Add("Data Source", stream.DbURL); builder.Add("Initial Catalog", stream.DbName); builder.Add("User Id", stream.DbUserName); builder.Add("Password", stream.DbPassword); _connectionString = builder.ConnectionString; /* be careful when updating the SQL below, the rdr uses field indexes, * if this wasn't so small and self contained, I probably wouldn't be so lazy, * buf for one field...well...moving on.*/ var sql = $@" select b.name as ColumnName, type_name(b.xusertype) ColumnType, b.IsNullable, columnproperty(a.id, b.name, 'isIdentity') IsIdentity, sm.text AS DefaultValue from sysobjects a inner join syscolumns b on a.id = b.id LEFT JOIN sys.syscomments sm ON sm.id = b.cdefault WHERE a.xtype = 'U' and a.name = @tableName"; var fields = new List <SQLFieldMetaData>(); using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn)) { cmd.Parameters.AddWithValue("@tableName", stream.DbTableName); try { await cn.OpenAsync(); using (var rdr = await cmd.ExecuteReaderAsync()) { while (await rdr.ReadAsync()) { fields.Add(new SQLFieldMetaData() { ColumnName = rdr["ColumnName"].ToString(), IsRequired = !Convert.ToBoolean(rdr["IsNullable"]), DataType = rdr["ColumnType"].ToString(), IsIdentity = Convert.ToBoolean(rdr["IsIdentity"]), DefaultValue = Convert.ToString(rdr["DefaultValue"]) }); } } } catch (Exception ex) { result.AddUserError($"Could not access SQL Server: {ex.Message}"); return(result); } } if (fields.Count == 0) { result.AddUserError($"Table [{stream.DbTableName}] name not found on SQL Server database [{stream.DbName}] on server [{stream.DbURL}."); } else { result.Concat(stream.ValidateSQLSeverMetaData(fields)); } return(result); }
public static Task <SqlConnection> ConnectTo(this SqlConnectionStringBuilder self) { return(ConnectTo(self.ConnectionString)); }
protected void GetDataBaseSchema(string ConnectionString) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(); builder.ConnectionString = ConnectionString; string server = builder.DataSource; string database = builder.InitialCatalog; connection.Open(); DataTable schemaTables = connection.GetSchema("Tables"); foreach (System.Data.DataRow rowTable in schemaTables.Rows) { String tableName = rowTable.ItemArray[2].ToString(); this.TableList.Add(tableName); string[] restrictionsColumns = new string[4]; restrictionsColumns[2] = tableName; DataTable schemaColumns = connection.GetSchema("Columns", restrictionsColumns); foreach (System.Data.DataRow rowColumn in schemaColumns.Rows) { string ColumnName = rowColumn[3].ToString(); this.ColumnList.Add(new Columns() { TableName = tableName, FieldName = ColumnName }); } string[] restrictionsPrimaryKey = new string[4]; restrictionsPrimaryKey[2] = tableName; DataTable schemaPrimaryKey = connection.GetSchema("IndexColumns", restrictionsColumns); foreach (System.Data.DataRow rowPrimaryKey in schemaPrimaryKey.Rows) { string indexName = rowPrimaryKey[2].ToString(); if (indexName.IndexOf("PK_") != -1) { this.PrimaryKeyList.Add(new PrimaryKey() { TableName = tableName, FieldName = rowPrimaryKey[6].ToString(), PrimaryKeyName = indexName }); } if (indexName.IndexOf("UQ_") != -1) { this.UniqueKeyList.Add(new UniqueKey() { TableName = tableName, FieldName = rowPrimaryKey[6].ToString(), UniqueKeyName = indexName }); } } string[] restrictionsForeignKeys = new string[4]; restrictionsForeignKeys[2] = tableName; DataTable schemaForeignKeys = connection.GetSchema("ForeignKeys", restrictionsColumns); foreach (System.Data.DataRow rowFK in schemaForeignKeys.Rows) { this.ForeignKeyList.Add(new ForeignKey() { ForeignName = rowFK[2].ToString(), TableName = tableName, // FieldName = rowFK[6].ToString() //There is no information }); } } } }
public object GetConnection(MapDataSet.durados_SqlConnectionRow sqlConnectionRow, int dataSourceTypeId, System.Data.SqlClient.SqlConnectionStringBuilder builder) { return(GetConnection(sqlConnectionRow, dataSourceTypeId, builder, GetConnectionStringTemplate(sqlConnectionRow))); }
public static void AddConnection(ref string s, string name, string connectionString) { System.Data.SqlClient.SqlConnectionStringBuilder cb = null; cb = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString); s += string.Format("<tr><td class=\"Name\" valign=\"top\">{0}:</td><td class=\"Value\" valign=\"top\">{1}.{2}</td></tr>", name, cb.DataSource, cb.InitialCatalog); }
public object GetConnection(MapDataSet.durados_SqlConnectionRow sqlConnectionRow, int dataSourceTypeId, System.Data.SqlClient.SqlConnectionStringBuilder builder, string template) { string connectionString = null; string serverName = null; bool? integratedSecurity = null; if (dataSourceTypeId == 2 || dataSourceTypeId == 4) { if (sqlConnectionRow.IsServerNameNull()) { serverName = builder.DataSource; } else { serverName = sqlConnectionRow.ServerName; } if (sqlConnectionRow.IsIntegratedSecurityNull()) { integratedSecurity = builder.IntegratedSecurity; } else { integratedSecurity = sqlConnectionRow.IntegratedSecurity; } } else { integratedSecurity = builder.IntegratedSecurity; serverName = builder.DataSource; } if (integratedSecurity.HasValue && integratedSecurity.Value) { connectionString = "Data Source={0};Initial Catalog={1};Integrated Security=True;"; return(string.Format(connectionString, serverName, sqlConnectionRow.Catalog)); } else { //connectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3};Integrated Security=False;"; connectionString = template; string username = null; string password = null; if (dataSourceTypeId == 2 || dataSourceTypeId == 4) { if (sqlConnectionRow.IsUsernameNull()) { username = builder.UserID; } else { username = sqlConnectionRow.Username; } if (sqlConnectionRow.IsPasswordNull()) { password = builder.Password; } else { password = sqlConnectionRow.Password; } } else { username = builder.UserID; password = builder.Password; } if (!sqlConnectionRow.IsProductPortNull()) { return(string.Format(connectionString, serverName, sqlConnectionRow.Catalog, username, password, sqlConnectionRow.ProductPort)); } else { return(string.Format(connectionString, serverName, sqlConnectionRow.Catalog, username, password)); } } }
public DatabaseConfig GenerateDBType() { DatabaseConfig config = new DatabaseConfig(); if (string.IsNullOrEmpty(MSSQLDatabase.Text)) { throw new Exception("Database must be fill in."); //return null; } config.DBType = WebUtils.DBTypeEmun.MSSQL; System.Data.SqlClient.SqlConnectionStringBuilder connStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(); connStringBuilder.DataSource = MSSQLServerLocation.Text; connStringBuilder.InitialCatalog = MSSQLDatabase.Text; connStringBuilder.UserID = MSSQLUserID.Text; connStringBuilder.Password = MSSQLPassword.Text; config.ConnectionString = connStringBuilder.ConnectionString; if (config.TestConnection()) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStringBuilder.ConnectionString); conn.Open(); DataTable table = conn.GetSchema("Tables"); conn.Close(); DataRow[] rows = table.Select("TABLE_NAME='SystemParameter'"); if (rows.GetLength(0) == 0) { if (chkCreateDatabase.Checked) { HROne.ProductVersion.Database.CreateTableAndData(AppDomain.CurrentDomain.BaseDirectory, config.ConnectionString); } else { throw new Exception("Database does not contain table"); } } return(config); } else { if (config.TestServerConnectionWithoutDatabase()) { if (chkCreateDatabase.Checked) { try { string saUser = SAUserID.Text; string saPassword = SAPassword.Text; if (saUser.Trim() == string.Empty) { saUser = MSSQLUserID.Text; saPassword = MSSQLPassword.Text; } connStringBuilder.InitialCatalog = string.Empty; connStringBuilder.UserID = saUser; connStringBuilder.Password = saPassword; HROne.ProductVersion.Database.CreateSchema(connStringBuilder.ConnectionString, MSSQLDatabase.Text, MSSQLUserID.Text); connStringBuilder.InitialCatalog = MSSQLDatabase.Text; HROne.ProductVersion.Database.CreateTableAndData(AppDomain.CurrentDomain.BaseDirectory, connStringBuilder.ConnectionString); return(config); } catch (Exception ex) { throw new Exception("Error on creating Database:\n" + ex.Message); } } else { throw new Exception("Invalid Database name."); } } else { throw new Exception("Fail to connect to server."); } } //return null; }