public dbtable(SqlConnection _database, string _TableName, string _TablePrefix) { DB = _database; command = new SqlCommand(); command.Connection = DB; TableName = _TableName; TablePrefix = _TablePrefix; DataTable tbl1 = DB.GetSchema("Tables"); List<string> lst = new List<string>(); foreach (DataRow dr in tbl1.Rows) { string name = dr["TABLE_NAME"].ToString(); lst.Add(name.ToLower()); } if (!lst.Contains(_TableName.ToLower())) throw new Exception("Таблица не найдена в базе данных"); else { DataTable tbl = DB.GetSchema("Columns", new[] { DB.Database, null, _TableName }); Fields = new Dictionary<string, string>(); foreach (DataRow dr in tbl.Rows) { string name = dr["COLUMN_NAME"].ToString(); string type = dr["DATA_TYPE"].ToString(); Fields.Add(name.ToLower(), type); } } }
public List <string> GetFieldInfoFromMeta(String strOwner, String strTableName) { try { string[] res = new string[4] { null, null, strTableName, null }; System.Data.DataTable tb = cnn.GetSchema("Columns", res); System.Data.DataRow[] rows = tb.Select("", "ORDINAL_POSITION"); List <string> lstFields = new List <string>(); for (int i = 0; i < rows.Length; i++) { string strFldName = rows[i]["Column_Name"].ToString(); String strDataType = rows[i]["Data_Type"].ToString(); String strLength = rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString(); String strPreci = rows[i]["NUMERIC_PRECISION"].ToString(); String strDot = rows[i]["NUMERIC_SCALE"].ToString(); if (strDataType == "7" || strDataType == "135") { continue; } lstFields.Add(strFldName); } return(lstFields); } catch (System.Exception ex) { } return(null); }
static internal List <SqlSchemaInfo> GetSchemaInfo(System.Data.SqlClient.SqlConnection con, List <SqlSchemaInfo> schemaList) { try { DataTable tbl = con.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables); foreach (DataRow row in tbl.Rows) { SqlSchemaInfo ssi = new SqlSchemaInfo(); ssi.Type = "TABLE"; ssi.Name = (string)row["table_name"]; schemaList.Add(ssi); } tbl = con.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Views); foreach (DataRow row in tbl.Rows) { SqlSchemaInfo ssi = new SqlSchemaInfo(); ssi.Type = "VIEW"; ssi.Name = (string)row["table_name"]; schemaList.Add(ssi); } } catch { } return(schemaList); }
static void Main(string[] args) { string directory = string.Empty; string server = string.Empty; string database = string.Empty; string userName = string.Empty; string password = string.Empty; foreach (string s in args) { if (s.Contains("/dir:")) directory = s.Substring(5); else if (s.Contains("/s:")) server = s.Substring(3); else if (s.Contains("/d:")) database = s.Substring(3); else if (s.Contains("/u:")) userName = s.Substring(3); else if (s.Contains("/p:")) password = s.Substring(3); } string connectionstring = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3}", server, database, userName, password); DataTable dt = new DataTable(); using (SqlConnection cn = new SqlConnection(connectionstring)) { cn.Open(); dt = cn.GetSchema("Tables"); foreach (DataRow row in dt.Rows) { DataTable columns = new DataTable(); string[] restrictions = new string[4]{ database, null, row[2].ToString(), null }; columns = cn.GetSchema("Columns", restrictions); Dictionary<string, string> col = new Dictionary<string, string>(); foreach (DataRow row2 in columns.Rows) { col.Add(row2[3].ToString(), row2[7].ToString()); } ClassMaker.MakeClass(directory, row[2].ToString(), col); } } }
public List<Database> GetAll() { using (var connection = new SqlConnection(new Connection().ConnectionString)) { try { connection.Open(); var dataTable = connection.GetSchema("Databases"); var dbList = new List<Database>(dataTable.Rows.Count); foreach (DataRow database in dataTable.Rows) { try { var name = database.Field<String>("database_name"); var dbID = database.Field<short>("dbid"); var createdDate = database.Field<DateTime>("create_date"); dbList.Add(new Database() { Created = createdDate, Name = name, ID = dbID }); } catch (Exception) { continue; } } return dbList; } catch (Exception) { return null; } } }
public bool ConnectToDataBase(string strConnect) { sqlConnection = new SqlConnection(strConnect.ToString()); try { sqlConnection.Open(); listTables = new List<string>(); using (DataTable dt = sqlConnection.GetSchema("Tables")) { if (dt != null && dt.Rows.Count > 0) { listTables.Capacity = dt.Rows.Count; foreach (DataRow row in dt.Rows) listTables.Add(row["table_name"].ToString()); } } sqlConnection.Close(); //string query = "select * from sys.tables where type_desc = 'USER_TABLE'"; } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); return false; } return true; }
public static DataSet GetMetadata(string server, string instance) { DataSet metadata = new DataSet(); SqlConnection connection = new SqlConnection(string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", server, instance)); connection.Open(); foreach (DataRow item in connection.GetSchema().Rows) { DataTable t = connection.GetSchema(item[0].ToString()); t.TableName = item[0].ToString(); metadata.Tables.Add(t); } // Now create the "view" that contains all of the goods that are needed to create classes metadata.Tables.Add(CreateTableView(metadata)); connection.Close(); return metadata; }
private void btnConnect_Click(object sender, EventArgs e) { var connectionString = GenerateConnectionString(); try { using (var connection = new SqlConnection(connectionString)) { if (connection.State != ConnectionState.Open) { connection.Open(); } var databases = connection.GetSchema("Databases"); foreach (DataRow database in databases.Rows) { var databaseName = database.Field<String>("database_name"); cbDatabases.Items.Add(databaseName); } cbDatabases.SelectedIndex = 0; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public void ProcurarBancos() { //Connection String string connection = String.Format("Data Source={0};Persist Security Info=True;User ID={1};Password={2};", txtServer.Text, txtUser.Text, txtPassword.Text); try { //Abrir a conexão com o banco de dados SqlConnection con = new SqlConnection(connection); con.Open(); DataTable bancos = con.GetSchema("Databases"); List<String> databases = new List<String>(); foreach (DataRow dr in bancos.Rows) databases.Add(dr["database_name"].ToString()); cboDatabases.DataSource = databases; } catch (Exception ex) { MessageBox.Show(String.Format("Ocorreu um erro:\n\r{0}", ex.ToString())); } }
private bool Load_Database() { try { if (txtNameServer.Text != "") { cboNameDatabase.Text = ""; cboNameDatabase.Items.Clear(); //using (var con = new SqlConnection("Data Source=" + txtNameServer.Text + "; Integrated Security=True;")) using (var con = new SqlConnection("Data Source=" + txtNameServer.Text + ";Persist Security Info=True;User ID=" + txtUser.Text + ";Password="******"")) { con.Open(); DataTable databases = con.GetSchema("Databases"); if (databases != null) { foreach (DataRow database in databases.Rows) { String databaseName = database.Field<String>("database_name"); short dbID = database.Field<short>("dbid"); if (databaseName != "master" && databaseName != "tempdb" && databaseName != "model" && databaseName != "msdb") cboNameDatabase.Items.Add(databaseName); } } con.Close(); } } return true; } catch { return false; } }
private IEnumerable<string> GetAllUserDatabases() { var databases = new List<String>(); DataTable databasesTable; using (var connection = new SqlConnection(_connectionString)) { connection.Open(); databasesTable = connection.GetSchema("Databases"); connection.Close(); } foreach (DataRow row in databasesTable.Rows) { string databaseName = row["database_name"].ToString(); if (_systemDatabaseNames.Contains(databaseName)) continue; databases.Add(databaseName); } return databases; }
public static List<DatabaseModel> GetListDatabase(string sqlServerName, string sqlUserLogin, string sqlUserPassword, bool fromSource, ref List<User> userModels) { var lstDatabase = new List<DatabaseModel>(); using ( var con = new SqlConnection(string.Format(Constants.SchemaConnection, sqlServerName, sqlUserLogin, sqlUserPassword)) ) { con.Open(); DataTable databases = con.GetSchema("Databases"); var temp = databases.Rows; foreach (DataRow database in databases.Rows) { var databaseName = database.Field<String>("database_name"); var dbID = database.Field<short>("dbid"); var creationDate = database.Field<DateTime>("create_date"); lstDatabase.Add(new DatabaseModel { DatabaseID = dbID, DatabaseName = databaseName, CreatedDate = creationDate, }); } if (fromSource) { userModels = GetListUserAdapt(con); } con.Close(); } return lstDatabase; }
public static List<string> GetDatabasesOnServer(string ServerName) { List<String> databases = new List<String>(); SqlConnectionStringBuilder connection = new SqlConnectionStringBuilder(); connection.DataSource = ServerName; //connection.UserID = //get username; // connection.Password = //get password; connection.IntegratedSecurity = true; String strConn = connection.ToString(); //create connection SqlConnection sqlConn = new SqlConnection(strConn); //open connection sqlConn.Open(); //get databases DataTable tblDatabases = sqlConn.GetSchema("Databases"); //close connection sqlConn.Close(); //add to list foreach (DataRow row in tblDatabases.Rows) { String strDatabaseName = row["database_name"].ToString(); databases.Add(strDatabaseName); } return databases; }
/// <summary> /// 连接SQL Server 数据库 /// </summary> /// <param name="filePath"></param> /// <returns>数据表表名</returns> public string[] SQLconection(string filePath) { string[] strTable = null; try { sqlCon = new SqlConnection("server = .; database = " + filePath + "; Trusted_Connection=SSPI "); sqlCon.Open(); DataTable shemaTable = sqlCon.GetSchema("Tables"); int n = shemaTable.Rows.Count; strTable = new string[n]; int m = shemaTable.Columns.IndexOf("TABLE_NAME"); for (int i = 0; i < n; i++) { DataRow m_DataRow = shemaTable.Rows[i]; strTable[i] = m_DataRow.ItemArray.GetValue(m).ToString(); } return strTable; } catch { return strTable; } }
public static bool TableExists(SqlConnection connection, string tableName) { var filter = new String[4]; filter[2] = tableName; filter[3] = "BASE TABLE"; DataTable dt = connection.GetSchema("Tables", filter); return dt.Rows.Count == 1; }
static internal List <SqlSchemaInfo> GetSchemaInfo(System.Data.SqlClient.SqlConnection con, List <SqlSchemaInfo> schemaList) { try { DataTable tbl = con.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables); foreach (DataRow row in tbl.Rows) { SqlSchemaInfo ssi = new SqlSchemaInfo(); ssi.Type = "TABLE"; string schema = row["table_schema"] as string; if (schema != null && schema != "dbo") { ssi.Name = string.Format("{0}.{1}", schema, (string)row["table_name"]); } else { ssi.Name = (string)row["table_name"]; } schemaList.Add(ssi); } tbl = con.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Views); foreach (DataRow row in tbl.Rows) { SqlSchemaInfo ssi = new SqlSchemaInfo(); ssi.Type = "VIEW"; string schema = row["table_schema"] as string; if (schema != null && schema != "dbo") { ssi.Name = string.Format("{0}.{1}", schema, (string)row["table_name"]); } else { ssi.Name = (string)row["table_name"]; } schemaList.Add(ssi); } } catch { } schemaList.Sort(); return(schemaList); }
public static DataSet GetCommonSchemaDataSet(SqlConnection DataBaseConnection) { DataSet CommonDataSet = new DataSet(); CommonDataSet.DataSetName = "SchemaDataSet"; CommonDataSet.Tables.Add(DataBaseConnection.GetSchema()); CommonDataSet.Tables[0].TableName = "Schema"; foreach (DataRow SchemaEntry in CommonDataSet.Tables[0].Rows) { String SchemaEntryName = SchemaEntry[0].ToString(); if (SchemaEntryName == "StructuredTypeMembers") continue; DataTable SchemaDetailTable = DataBaseConnection.GetSchema(SchemaEntryName); SchemaDetailTable.TableName = SchemaEntryName; CommonDataSet.Tables.Add(SchemaDetailTable); } // CompleteWithExtendedSchemaData (DataBaseConnection, CommonDataSet); return CommonDataSet; }
private string getSchemaName(string tableName) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ctx.Connection.ConnectionString + "password=abcd1234;")) { conn.Open(); DataTable allTablesSchemaTable = conn.GetSchema("Tables"); return(allTablesSchemaTable.Rows.Cast <DataRow>().Where(w => w["TABLE_NAME"].ToString() == tableName).Select(s => s["TABLE_SCHEMA"].ToString()).First()); } }
public static List<string> GetTableNames(SqlConnection conn) { DataTable schema = conn.GetSchema("Tables"); var result = new List<string>(); foreach (DataRow row in schema.Rows) { if (string.Equals("BASE TABLE", row[3].ToString(), StringComparison.InvariantCultureIgnoreCase)) result.Add(string.Format("[{0}].[{1}]", row[1], row[2])); } return result; }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { SqlConnection conn = null; using (conn = new SqlConnection(CONN_STRING_BEETLE)) { conn.Open(); // 获取数据库架构信息 //gvSchema.DataSource = conn.GetSchema(); //gvSchema.DataBind(); var dt = new DataTable(); dt.Columns.Add("elementoriginalfilename"); dt.Columns.Add("elementimageurl"); var row = dt.NewRow(); row[0] = "梵高章鱼贴iphone4-梵高-夜空下的咖啡店-C.png"; row[1] = @"Element\艺术类\背景\20130911170029_1.png"; dt.Rows.Add(row); row = dt.NewRow(); row[0] = "梵高章鱼贴iphong4-梵高-夜空下的咖啡店-A.png"; row[1] = @"Element\艺术类\背景\20130906140254_4.png"; dt.Rows.Add(row); row = dt.NewRow(); row[0] = "梵高章鱼贴iphong4-梵高-夜空下的咖啡店-B.png"; row[1] = @"Element\艺术类\背景\20130906140254_3.png"; dt.Rows.Add(row); gvSchema.DataSource = dt; gvSchema.DataBind(); // 获取所有表 //gvTables.DataSource = conn.GetSchema(SqlClientMetaDataCollectionNames.Tables); //gvTables.DataBind(); var dv = dt.DefaultView; dv.Sort = "elementimageurl desc"; dt = dv.ToTable(); gvTables.DataSource = dt; gvTables.DataBind(); // 获取所有存储过程 gvProcedures.DataSource = conn.GetSchema(SqlClientMetaDataCollectionNames.Procedures); gvProcedures.DataBind(); conn.Close(); } } }
public DataTable getDatabaseNames() { String TargetConnString = String.Format("server = SCSBWIN-385575\\SQLEXPRESS;Integrated Security = true;"); using (SqlConnection con = new SqlConnection(TargetConnString)) { con.Open(); DataTable databases = con.GetSchema("Databases"); return databases; } }
private void ExtractSchemaDetail(SqlConnection connection, string collectionName, string columnName, CommandType type) { DataTable dt = connection.GetSchema(collectionName); foreach (DataRow row in dt.Rows) { if (!dbObjects.ContainsKey(row[columnName].ToString())) { dbObjects.Add(row[columnName].ToString(), type); } } }
private void Action1_Click(object sender, EventArgs e) { SqlConnection sqlcnn = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=;Initial Catalog=TestForSmartTools;"); SqlConnection sqlcnn2 = new SqlConnection("Data Source=.;Persist Security Info=True;User ID=sa;Password=;Initial Catalog=TestForSmartTools;"); OleDbConnection olecnn = new OleDbConnection(); //olecnn.GetOleDbSchemaTable( //sqlcnn.GetSchema( SqlCommand sqlcmd = new SqlCommand("select TOP 1 * from TABLE1", sqlcnn2); SqlCommand sqlcmd2 = new SqlCommand("CustOrderHist2", sqlcnn); sqlcmd2.CommandType = CommandType.StoredProcedure; sqlcmd2.Parameters.Add(new SqlParameter("@CustomerID", "AROUT")); sqlcnn.Open(); sqlcnn2.Open(); SqlDataReader sqldr = sqlcmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable stb = sqldr.GetSchemaTable(); sqldr.Close(); /* SqlCommand sqlcmd3 = new SqlCommand("CustOrderHist2", sqlcnn); sqlcmd3.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters(sqlcmd3); */ DataTable stb2; DataTable stb3; stb2 = sqlcnn2.GetSchema("ProcedureParameters", new string[] { "Northwind", null, "CustOrderHist3", null }); //stb2 = sqlcnn2.GetSchema("Restrictions"); /* stb3 = sqlcnn2.GetSchema("Columns", new string[] { "Northwind", "dbo", "TABLE1", null }); foreach (DataColumn dc in stb.Columns) { Console.WriteLine(dc.DataType.ToString()); } */ dataGridView1.DataSource = stb2; dataGridView2.DataSource = stb; sqlcnn.Close(); sqlcnn2.Close(); //SqlConnection sqlcnn3 = new SqlConnection(@"Data Source=WANGY\SQLEXPRESS;Initial Catalog=temp;Integrated Security=True"); //SqlCommand sqlcmd3 = new SqlCommand("select TOP 0 * from Table_3", sqlcnn3); //sqlcnn3.Open(); //SqlDataReader sqldr3 = sqlcmd3.ExecuteReader(CommandBehavior.KeyInfo); //DataTable stb4 = sqldr3.GetSchemaTable(); //sqldr3.Close(); //dataGridView3.DataSource = stb4; //sqlcnn3.Close(); }
private static void Execute(SqlConnection conn, SqlCommand cmd, DataTable dt) { ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Sample"]; using (conn = new SqlConnection(settings.ConnectionString)) { conn.Open(); dt = conn.GetSchema(); cmd.Connection = conn; cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.Dispose(); } }
static void GetSchema_MetaDataCollections (SqlConnection conn) { object [][] expectedColumns = { new object [] { "CollectionName", typeof (string) }, new object [] { "NumberOfRestrictions", typeof (int) }, new object [] { "NumberOfIdentifierParts", typeof (int) } }; object [][] expectedRows = { new object [] { "MetaDataCollections", 0, 0 }, new object [] { "DataSourceInformation", 0, 0 }, new object [] { "DataTypes", 0, 0 }, new object [] { "Restrictions", 0, 0 }, new object [] { "ReservedWords", 0, 0 }, new object [] { "Users", 1, 1 }, new object [] { "Databases", 1, 1 }, new object [] { "Tables", 4, 3 }, new object [] { "Columns", 4, 4 }, new object [] { "StructuredTypeMembers", 4, 4 }, new object [] { "Views", 3, 3 }, new object [] { "ViewColumns", 4, 4 }, new object [] { "ProcedureParameters", 4, 1 }, new object [] { "Procedures", 4, 3 }, new object [] { "ForeignKeys", 4, 3 }, new object [] { "IndexColumns", 5, 4 }, new object [] { "Indexes", 4, 3 }, new object [] { "UserDefinedTypes", 2, 1 } }; using (DataTable dt = conn.GetSchema ()) { Assert.IsNotNull (dt, "#A1"); Assert.AreEqual ("MetaDataCollections", dt.TableName, "#A2"); Assert.AreEqual (expectedColumns.Length, dt.Columns.Count, "#A3"); Assert.AreEqual (expectedRows.Length, dt.Rows.Count, "#A4"); for (int i = 0; i < expectedColumns.Length; i++) { DataColumn column = dt.Columns [i]; object [] expectedColumn = expectedColumns [i]; Assert.AreEqual (expectedColumn [0], column.ColumnName, "#A5:" + i); Assert.AreEqual (expectedColumn [1], column.DataType, "#A6:" + i); } for (int i = 0; i < expectedRows.Length; i++) { DataRow row = dt.Rows [i]; object [] expectedRow = expectedRows [i]; for (int j = 0; j < expectedColumns.Length; j++) Assert.AreEqual (expectedRow [j], row [j], "#A7: " + i + "," + j); } } }
public List<string> GetDatabases(string server, string username, string password) { var list = new List<string>(); using (var conn = new SqlConnection(string.Format("server={0};uid={1};pwd={2}", server, username, password))) { conn.Open(); var databases = conn.GetSchema("Databases"); list.AddRange(from DataRow row in databases.Rows select row["database_name"].ToString()); } return list; }
//helper method that returns a list of the tables in the database private static List<string> GetTables(string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); DataTable schema = connection.GetSchema("Tables"); List<string> TableNames = new List<string>(); foreach (DataRow row in schema.Rows) { TableNames.Add(row[2].ToString()); } return TableNames; } }
//Parameters: // SqlConnection: // i.e new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ToString()); public ConnectedCRUD(SqlConnection mSqlConnection) { this.mSqlConnection = mSqlConnection; this.mSqlDataAdapter = new SqlDataAdapter(); this.tables = new List<string>(); mSqlConnection.Open(); DataTable mDataTable = mSqlConnection.GetSchema("Tables"); mSqlConnection.Close(); foreach (DataRow row in mDataTable.Rows) { string tablename = (string)row[2]; this.tables.Add(tablename); } }
public List<string> GetTables() { using (SqlConnection connection = new SqlConnection(ConString)) { connection.Open(); DataTable schema = connection.GetSchema("Tables"); List<string> TableNames = new List<string>(); foreach (DataRow row in schema.Rows) { TableNames.Add(row[2].ToString()); } ComboBox1.ItemsSource = TableNames; return TableNames; } }
private void ExtractSchemaDetail(string connectionString, SqlConnection connection, string collectionName, string columnName, CommandType type) { DataTable dt = connection.GetSchema(collectionName); dbObjects = new SortedList<string, CommandType>(); cacheData.Add(connectionString, dbObjects); foreach (DataRow row in dt.Rows) { if (!dbObjects.ContainsKey(row[columnName].ToString().ToLowerInvariant())) { dbObjects.Add(row[columnName].ToString().ToLowerInvariant(), type); } } }
public static DataSet GetSimpleSchemaDataSet(SqlConnection DataBaseConnection) { try { DataSet SimpleDataSet = new DataSet(); SimpleDataSet.DataSetName = "SchemaDataSet"; SimpleDataSet.Tables.Add(DataBaseConnection.GetSchema()); SimpleDataSet.Tables[0].TableName = "Schema"; return SimpleDataSet; } catch (Exception Excp) { return null; } }
public static IList<string> ListTables() { using (SqlConnection conn = new SqlConnection(Data.Properties.Settings.Default.PortalMSPConnectionString1)) { conn.Open(); List<string> tables = new List<string>(); DataTable dt = conn.GetSchema("Tables"); foreach (DataRow row in dt.Rows) { string tableName = (string)row[2]; tables.Add(tableName); } return tables; } }
public void PopulateSQLDatabases() { cmbDatabase.Items.Clear(); String connString = ""; try { if (radWindowsAuth.Checked == true) { connString = "Data Source=" + cmbServers.SelectedItem.ToString() + "; Integrated Security=True;"; } else { connString = "Data Source=" + cmbServers.SelectedItem.ToString() + "; uid=" + txtUsername.Text.Trim() + "; pwd=" + txtPassword.Text.Trim() + ";"; } using (var sqlConx = new SqlConnection(connString)) { try { sqlConx.Open(); var tblDatabases = sqlConx.GetSchema("Databases"); sqlConx.Close(); foreach (DataRow row in tblDatabases.Rows) { cmbDatabase.Items.Add(row["database_name"].ToString()); } } catch (InvalidOperationException) { //MessageBox.Show("The connection to SQL Server could not be established.","SQL Error",MessageBoxButtons.OK,MessageBoxIcon.Error,MessageBoxDefaultButton.Button1); //groupBox2.Enabled = false; } catch (SqlException) { //MessageBox.Show("The connection to SQL Server could not be established.", "SQL Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1); //groupBox2.Enabled = false; } } } catch { } }
// GET: api/Insert public IEnumerable<string> Get() { var connectionString = ConfigurationManager.ConnectionStrings["kitacolle"].ConnectionString; int id = 0; float ondo = 0; using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = connectionString; connection.Open(); var aa = connection.Database; DataTable bb = connection.GetSchema("Tables"); var command = new SqlCommand(); command.CommandText = "SELECT * FROM dbo.Kitacolle WHERE 30<Ondo"; command.Connection = connection; //var reader2 = command.ExecuteReader(); var reader = command.ExecuteReader(); Thread.Sleep(5000); if (reader.HasRows) { while (reader.Read()) { int kari1 = reader.GetInt32(0); var temp = reader.GetValue(1); var kari2 = float.Parse(temp.ToString()); Debug.Print("{0} {1}", kari1, kari2); id = kari1; ondo = kari2; } } Debug.WriteLine("State: {0}", connection.State); Debug.WriteLine("ConnectionString: {0}", connection.ConnectionString); } return new string[] { id.ToString(),ondo.ToString() }; }
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) { throw new Exception("Database does not contain table"); } return(config); } else { if (config.TestServerConnectionWithoutDatabase()) { throw new Exception("Invalid Database name."); } else { throw new Exception("Fail to connect to server."); } } //return null; }
//Функция FillDatabaseCB заполняет ComboBox именами таблиц базы данных public void FillDatabaseCB(ComboBox cb) { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(s); con.Open(); cb.Items.Clear(); var table = con.GetSchema("Tables"); foreach (DataRow row in table.Rows) { cb.Items.Add(row["TABLE_NAME"]); } if (cb.Items.Count != 0) { TableName = cb.Items[0].ToString(); access = 2; } else { access = 1; } con.Close(); }
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; }
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; }