Пример #1
0
        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);
                }
            }
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
                }
            }
        }
Пример #5
0
 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;
 }
Пример #8
0
        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);
            }
        }
Пример #9
0
        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()));
            }
        }
Пример #10
0
 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; }
 }
Пример #11
0
        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;
        }
Пример #12
0
 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;
 }
Пример #13
0
        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;
            }
        }
Пример #15
0
 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);
        }
Пример #17
0
		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;
			}
Пример #18
0
    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());
        }
    }
Пример #19
0
 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;
 }
Пример #20
0
    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();
        }
      }
    }
Пример #21
0
    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;
        }
    }
Пример #22
0
        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);
                }
            }
        }
Пример #23
0
        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();
        }
Пример #24
0
 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();
     }
 }
Пример #25
0
Файл: test.cs Проект: mono/gert
	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);
			}
		}
	}
Пример #26
0
        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;
        }
Пример #27
0
 //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;
     }
 }
Пример #30
0
        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);
                }
            }
        }
Пример #31
0
		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;
				}
			}
Пример #32
0
		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;
			}
		}
Пример #33
0
        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
            {
            }
        }
Пример #34
0
        // 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;
    }
Пример #36
0
        //Функция 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;
    }
Пример #38
0
    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;

    }