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