public int UpDate_data(string Ma2, string name, DateTime date, string sex, string diachi, string sdt, string Ma1) { string query = " UPDATE dbo.DocGia SET MaDG=@ma2, TenDG=@name,NgaySinh=@date,GioiTinh=@sex,DiaChi=@diachi,SDT=@sdt WHERE MaDG=@ma1"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(Form1.checkConnectionString); try { using (SqlConnection connection = new SqlConnection(con)) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = query; // insert value of Song in database cmd.Parameters.Add("@ma2", SqlDbType.Char).Value = Ma2; cmd.Parameters.Add("@ma1", SqlDbType.Char).Value = Ma1; cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name; cmd.Parameters.Add("@date", SqlDbType.Date).Value = date.ToShortDateString().ToString(); cmd.Parameters.Add("@sdt", SqlDbType.Char).Value = sdt; cmd.Parameters.Add("@sex", SqlDbType.NChar).Value = sex; cmd.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = diachi; cmd.ExecuteNonQuery(); connection.Close(); return(1); } } catch (Exception e) { MessageBox.Show("Cập nhật không thành công, vui lòng kiểm tra lại!"); return(0); } }
private void txt_search_TextChanged(object sender, EventArgs e) { string query = "select MaSP as'Mã sản phẩm', TenSP as 'Tên sản phẩm',SoLuong as'Số lượng', GiaBan as'Giá bán',NSX from tblSanPham " + "where MaSP like N'%" + txt_search.Text + "%' or TenSP like N'%" + txt_search.Text + "%' or NSX like N'%" + txt_search.Text + "%'"; dtgv_thongke.Columns.Clear(); ConnectString b = new ConnectString(); string con = b.getConnectionString(fromdangnhap.checkConnectionString); using (SqlConnection conn = new SqlConnection(con)) { conn.Open(); DataSet data = new DataSet(); SqlDataAdapter adap = new SqlDataAdapter(query, conn); adap.Fill(data); dtgv_thongke.DataSource = data.Tables[0]; conn.Close(); } // using (SqlConnection conn = new SqlConnection(cnn.getConnectionString(1))) // { // dt.Clear(); // adap = new SqlDataAdapter(query, conn); // adap.Fill(dt); // dtgv_thongke.DataSource = dt; //} }
private void btn_MuonSach_Click(object sender, EventArgs e) { try { ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(Form1.checkConnectionString); using (SqlConnection connect = new SqlConnection(con)) { connect.Open(); string query = "exec MuonSach '" + txt_MaDG.Text.Trim() + "', '" + dtp_NgayMuon.Value + "', '" + txt_MaPhieu.Text.Trim() + "'"; SqlCommand cmd = connect.CreateCommand(); cmd.CommandText = query; cmd.ExecuteNonQuery(); MessageBox.Show("Mượn thành công!"); this.Close(); connect.Close(); } } catch { MessageBox.Show("Mượn sách thất bại"); } }
string Check_MaTs(string TenTs) { string MaSach = ""; try { DataTable data_Sach = new DataTable(); string query = "exec DS_Sach " + "'" + TenTs + "'"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(Form1.checkConnectionString); using (SqlConnection connect = new SqlConnection(con)) { connect.Open(); SqlDataAdapter apter = new SqlDataAdapter(query, connect); apter.Fill(data_Sach); connect.Close(); } MaSach = data_Sach.Rows[0]["MaCS"].ToString(); } catch { MaSach = ""; } return(MaSach); }
private void Connect_Click(object sender, EventArgs e) { if (CheckMethod()) { string _textHost = this.textHost.Text.Trim(); string _textUserId = this.textUserId.Text.Trim(); string _textPassword = this.textPassword.Text.Trim(); string _textport = this.textport.Text.Trim(); string _textInstance = this.textInstance.Text.Trim(); ConnectString conn = new ConnectString() { Host = _textHost, Port = _textport, Instance = _textInstance, UserId = _textUserId, Password = _textPassword }; try { app.TestConnection(conn); MessageBox.Show("连接成功"); } catch (Exception ex) { MessageBox.Show("连接失败"); MessageBox.Show(ex.Message); } } }
public void Test_Blob() { ConnectString source = new ConnectString { Host = "192.168.0.120", Port = "1521", Instance = "orcl", UserId = "masgh", Password = "******" }; ConnectString dest = new ConnectString { Host = "192.168.0.205", Port = "1521", Instance = "orcl", UserId = "masgh", Password = "******" }; string table = "T5399"; Table tableDetail = Tables.GetTableDetails(source, table); MDataSet dataSet = new MDataSet(tableDetail.Name, tableDetail.Columns.Select(item => item.Name).ToArray()); System.Data.IDataReader dr = Stores.Read(source, table); dataSet.SetDataSource(dr); Stores.Write(dest, dataSet); }
/// <summary> /// 判断表是否已存在 /// </summary> /// <param name="connString">普通用户</param> /// <param name="table"></param> /// <returns></returns> public static bool IsExists(ConnectString connString, string tableName) { string sql = $"select 1 from dba_tables where table_name = '{tableName}'"; object result = new DataGateway(OracleProvider.ManagedDataAccess).ExecuteScalar(connString, sql); return(result != null && Convert.ToInt16(result) == 1); }
/// <summary> /// 获取表的约束信息 /// </summary> /// <param name="connString"></param> /// <param name="tableName"></param> /// <returns></returns> public static IEnumerable <Constraint> GetConstraints(ConnectString connString, string tableName) { DataGateway gateway = new DataGateway(OracleProvider.ManagedDataAccess); string sql = "select owner, constraint_name, constraint_type, TABLE_NAME, r_constraint_name, delete_rule, " + "case when (status='ENABLED') then 1 else 0 end as enabled, " + "case when (index_name is not null) then 1 else 0 end as using_index " + $"from user_constraints where constraint_type!='C' and table_name='{tableName}'"; IEnumerable <Constraint> result = gateway.Query <Constraint>(connString, sql); sql = $"select * from USER_CONS_COLUMNS where table_name='{tableName}' order by column_name,position"; IEnumerable <ConstraintColumns> cols = gateway.Query <ConstraintColumns>(connString, sql); foreach (var con in result) { con.Columns = string.Join(",", cols.Where(item => item.Name == con.Name).OrderBy(item => item.Position).Select(item => item.ColumnName)); if (con.Type == "R") { con.RefConstraint = gateway.Query <ConstraintColumns>(connString, $"select * from USER_CONS_COLUMNS where constraint_name='{con.RefName}'").FirstOrDefault(); } } return(result); }
void ThongTinDocGia(string MaDG) { try { query = "select * from DocGia where MaDG like '" + MaDG + "'"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(Form1.checkConnectionString); DataTable data = new DataTable(); using (SqlConnection connect = new SqlConnection(con)) { connect.Open(); SqlDataAdapter apter = new SqlDataAdapter(query, connect); apter.Fill(data); txt_HoTen.Text = data.Rows[0]["TenDG"].ToString(); txt_DiaChi.Text = data.Rows[0]["DiaChi"].ToString(); txt_GioiTinh.Text = data.Rows[0]["GioiTinh"].ToString(); txt_SDT.Text = data.Rows[0]["SDT"].ToString(); dtp_NgaySinh.Value = Convert.ToDateTime(data.Rows[0]["NgaySinh"].ToString()); connect.Close(); } } catch { } }
/// <summary> /// 删除表,包括约束 /// </summary> /// <param name="connString"></param> /// <param name="tableName"></param> public static void DropTable(ConnectString connString, string tableName) { // TODO: 与约束的关系没有处理 string sql = $"drop table {tableName} cascade constraints"; new DataGateway(OracleProvider.ManagedDataAccess).ExecuteNonQuery(connString, sql.Trim()); }
/// <summary> /// 获取当前表空间下的所有约束信息 /// </summary> /// <param name="connString"></param> /// <returns></returns> public static IEnumerable <Constraint> GetOwnerConstraints(ConnectString connString) { string sql = "select owner, constraint_name, constraint_type, TABLE_NAME " + $"from user_constraints where constraint_type!='C'"; return(new DataGateway(OracleProvider.ManagedDataAccess).Query <Constraint>(connString, sql)); }
public int insert_data(string Ma, string name, DateTime date, string sex, string diachi, string sdt) { string query = "INSERT dbo.DocGia( MaDG ,TenDG ,NgaySinh ,GioiTinh ,DiaChi ,SDT) VALUES ( @ma , @name ,@date , @sex , @diachi , @sdt)"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(Form1.checkConnectionString); try { using (SqlConnection connection = new SqlConnection(con)) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = query; // insert value of Song in database cmd.Parameters.Add("@ma", SqlDbType.Char).Value = Ma; cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name; cmd.Parameters.Add("@date", SqlDbType.Date).Value = date.ToShortDateString().ToString(); cmd.Parameters.Add("@sdt", SqlDbType.Char).Value = sdt; cmd.Parameters.Add("@sex", SqlDbType.NChar).Value = sex; cmd.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = diachi; cmd.ExecuteNonQuery(); connection.Close(); return(1); } } catch (Exception e) { MessageBox.Show("Thêm không thành công, vui lòng kiểm tra lại!"); return(0); } }
public static IEnumerable <Tablespace> GetTableSpaces(ConnectString connstring) { string sql = "SELECT DISTINCT a.TABLESPACE_NAME,b.FILE_NAME, b.bytes,b.AUTOEXTENSIBLE ,b.INCREMENT_BY ,b.MAXBYTES from DBA_DATA_FILES b, dba_tablespaces a where a.tablespace_name = b.tablespace_name"; Debug.WriteLine(sql); return(new DataGateway(OracleProvider.ManagedDataAccess).Query <Tablespace>(connstring, sql)); }
/// <summary> /// 创建约束 /// </summary> /// <param name="connString"></param> /// <param name="cons"></param> public static void CreateConstraints(ConnectString connString, Constraint cons) { string sql = $"alter table {cons.TableName} add constraint {cons.Name} "; switch (cons.Type) { case "P": case "U": string key = cons.Type == "P" ? "primary key" : "unique"; sql += $"{key} ({cons.Columns}) "; if (cons.UsingIndex) { sql += $"using index tablespace {cons.Tablespace} " + $"pctfree 10 initrans 2 maxtrans 255 storage(initial 64K next 1M minextents 1 maxextents unlimited)"; } break; case "R": sql += $"foreign key ({cons.Columns}) references {cons.RefConstraint.TableName} ({cons.RefConstraint.ColumnName}) on delete {cons.DeleteRule}"; break; } Debug.WriteLine(sql); new DataGateway(OracleProvider.ManagedDataAccess).ExecuteNonQuery(connString, sql.Trim()); }
/// <summary> /// 根据表的基本信息和字段集合创建表 /// </summary> /// <param name="connString">dba用户</param> /// <param name="table"></param> public static void CreateTable(ConnectString connString, Table table) { List <string> sqls = new List <string>(); string colSql = string.Join(",", table.Columns.Select(item => CreateColumnSQL(item))); string sql = $"create Table {table.Name} ({colSql}) tablespace {table.TablespaceName} " + "pctfree 10 initrans 1 maxtrans 255 storage(initial 64K minextents 1 maxextents unlimited)"; sqls.Add(sql); if (!string.IsNullOrEmpty(table.Comments)) { sqls.Add($"comment on table {table.Name} is '{table.Comments}'"); } foreach (var col in table.Columns) { if (!string.IsNullOrEmpty(col.Comments)) { sqls.Add($"comment on column {table.Name}.{col.Name} is '{col.Comments}'"); } } new DataGateway(OracleProvider.ManagedDataAccess).ExecuteTransction(connString, sqls); }
public int insert() { string Id = txtma.Text; string name = txtten.Text; string nam = txtnamxb.Text; string nxb = txtnxb.Text; string kesach = txtkesach.Text; string theloai = txttheloai.Text; string query = "exec Insert_TS '" + Id + "','" + name + "','" + nam + "','" + theloai + "','" + nxb + "','" + kesach + "','" + "1000'"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(Form1.checkConnectionString); try { using (SqlConnection connection = new SqlConnection(con)) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = query; cmd.ExecuteNonQuery(); connection.Close(); return(1); } } catch (Exception e) { MessageBox.Show("Thêm không thành công, vui lòng kiểm tra lại!"); return(0); } }
/// <summary> /// 获取表空间文件的存储目录 /// </summary> /// <param name="connString"></param> /// <returns></returns> public static string GetStorePath(ConnectString connString) { string sql = "select name from v$datafile"; string filePath = (string)new DataGateway(OracleProvider.ManagedDataAccess).ExecuteScalar(connString, sql); return(Path.GetDirectoryName(filePath)); }
public bool ValidarConexao(ConnectString obj) { var comando = PostgresCommand.Pg_isReady(obj).MontarComando(@"C:\Program Files\PostgreSQL\11\bin\"); var retorno = _comando.Execute(comando); return(true); }
/// <summary> /// 用户授权 /// </summary> /// <param name="connString"></param> /// <param name="userName"></param> /// <param name="roles"></param> public static void Grant(ConnectString connString, string userName, string[] roles) { string joinedRole = string.Join(",", roles); string sql = $"GRANT {joinedRole} TO {userName}"; new DataGateway(OracleProvider.ManagedDataAccess).ExecuteNonQuery(connString, sql.Trim()); }
public static IEnumerable <User> GetAllUser(ConnectString connString) { string sql = "SELECT username,password,default_tablespace,TEMPORARY_TABLESPACE FROM dba_users"; Debug.WriteLine(sql); return(new DataGateway(OracleProvider.ManagedDataAccess).Query <User>(connString, sql)); }
/// <summary> /// 获取某表空间的相关信息(数据文件大小,名称) /// </summary> /// <param name="connstring"></param> /// <returns></returns> public static IEnumerable <Tablespace> GetTableSpacesInfo(ConnectString connstring, string tablespaceName) { string sql = "SELECT DISTINCT b.file_name ,a.INITIAL_EXTENT , b.AUTOEXTENSIBLE ,b.bytes,b.INCREMENT_BY ,b.MAXBYTES from DBA_DATA_FILES b, dba_tablespaces a " + $"where a.tablespace_name = b.tablespace_name and a.TABLESPACE_NAME = '{tablespaceName}'"; Debug.WriteLine(sql); return(new DataGateway(OracleProvider.ManagedDataAccess).Query <Tablespace>(connstring, sql)); }
/// <summary> /// 用户是否存在 /// </summary> /// <param name="connString"></param> /// <param name="userName"></param> /// <returns></returns> public static bool IsExists(ConnectString connString, string userName) { string sql = $"select 1 from all_users where username='******'"; object result = new DataGateway(OracleProvider.ManagedDataAccess).ExecuteScalar(connString, sql); return(result != null && Convert.ToInt16(result) == 1); }
/// <summary> /// 获取当前表空间的所有表 /// </summary> /// <param name="connString">普通用户</param> /// <returns></returns> public static IEnumerable <Table> GetOwnerTables(ConnectString connString) { string sql = "select t.object_id, t.OBJECT_NAME, p.tablespace_name, c.comments, t.created " + "from user_objects t, user_all_tables p, user_tab_comments c " + "where t.object_name= p.table_name and t.object_name= c.table_name " + "and t.object_type = 'TABLE'"; return(new DataGateway(OracleProvider.ManagedDataAccess).Query <Table>(connString, sql)); }
/// <summary> /// 删除表空间 /// </summary> /// <param name="connString"></param> /// <param name="spaceName"></param> /// <param name="dropDataFiles">是否删除数据文件</param> public static void DropTablespace(ConnectString connString, string spaceName, bool dropDataFiles = true) { string sql = $"drop tablespace {spaceName}"; if (dropDataFiles) { sql += " including contents and datafiles"; } new DataGateway(OracleProvider.ManagedDataAccess).ExecuteNonQuery(connString, sql.Trim()); }
/// <summary> /// 创建表空间 /// </summary> /// <param name="connString"></param> /// <param name="space"></param> public static void CreateTablespace(ConnectString connString, Model.Tablespace space) { string sql = $"create tablespace {space.Name} logging datafile '{space.Path}' " + $"size {space.Size}m " + string.Format(space.AutoExtend ? "autoextend on next {0}m " : "autoextend off ", space.ExtendSize) + $"maxsize {space.MaxSize}m " + $"extent management local"; new DataGateway(OracleProvider.ManagedDataAccess).ExecuteNonQuery(connString, sql.Trim()); }
public int insert() { string name = gunaTxtTen.Text; string sdt = gunaTxtSdt.Text; string Diachi = gunaTxtDiachi.Text; string gioitinh; if (gunaCheckBoxNam.Checked == true) { gioitinh = "Nam"; } else { gioitinh = "Nu"; } string tendangnhap = gunaTxtTdn.Text; if (name == "" || tendangnhap == "") { MessageBox.Show("vui lòng điền đầy đủ thông tin"); return(0); } string query = " INSERT dbo.tblNhanVien( TenDn ,MatKhau ,TenNV ,GT ,DiaChi ,SDT) VALUES (@tendangnhap , @matkhau , @name, @gt, @diachi, @sdt)"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(fromdangnhap.checkConnectionString); try { using (SqlConnection connection = new SqlConnection(con)) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = query; // insert value of Song in database cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name; cmd.Parameters.Add("@sdt", SqlDbType.NVarChar).Value = sdt; cmd.Parameters.Add("@gt", SqlDbType.NVarChar).Value = gioitinh; cmd.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = Diachi; cmd.Parameters.Add("@tendangnhap", SqlDbType.NChar).Value = tendangnhap; cmd.Parameters.Add("@matkhau", SqlDbType.NVarChar).Value = "123456"; cmd.ExecuteNonQuery(); connection.Close(); return(1); } } catch (Exception e) { MessageBox.Show("Thêm không thành công, vui lòng kiểm tra lại!"); return(0); } }
/// <summary> /// 获取默认表空间 /// </summary> /// <param name="connString"></param> //public void GetDefault(ConnectString connString) //{ // //TODO: 在展示层不要直接调用dal层 // string sql = "select * from user_tablespaces"; // DataGateway.ExecuteQuery(connString, sql); //} /// <summary> /// 获取临时表空间 /// </summary> /// <param name="connString"></param> //public void GetTemporary(ConnectString connString) //{ // //TODO: 在展示层不要直接调用dal层 // string sql = "select * from user_tablespaces"; // DataGateway.ExecuteQuery(connString,sql); //} public void Drop(ConnectString connString, string spaceName) { try { Tablespaces.DropTablespace(connString, spaceName); } catch (Exception ex) { throw ex; } }
public void Grant(ConnectString connString, string userName, string[] roles) { try { Users.Grant(connString, userName, roles); } catch (Exception ex) { throw ex; } }
public IEnumerable <User> GetAllUser(ConnectString connstring) { try { return(Users.GetAllUser(connstring)); } catch (Exception ex) { throw ex; } }
public int Update() { string name = gunaTxtTen.Text; string sdt = gunaTxtSdt.Text; string Diachi = gunaTxtDiachi.Text; string gioitinh; if (gunaCheckBoxNam.Checked == true) { gioitinh = "Nam"; } else { gioitinh = "Nu"; } string tendangnhap = gunaTxtTdn.Text; if (name == "" || tendangnhap == "") { MessageBox.Show("vui lòng điền đầy đủ thông tin"); return(0); } string query = "UPDATE dbo.tblNhanvien SET TenNV=@name,GT=@gt,DiaChi=@diachi,SDT=@sdt where TenDn = @tendn"; ConnectString cnn = new ConnectString(); string con = cnn.getConnectionString(fromdangnhap.checkConnectionString); try { using (SqlConnection connection = new SqlConnection(con)) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = query; // insert value of Song in database cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name; cmd.Parameters.Add("@sdt", SqlDbType.NVarChar).Value = sdt; cmd.Parameters.Add("@gt", SqlDbType.NVarChar).Value = gioitinh; cmd.Parameters.Add("@diachi", SqlDbType.NVarChar).Value = Diachi; cmd.Parameters.Add("@tendn", SqlDbType.NVarChar).Value = tendangnhap; cmd.ExecuteNonQuery(); connection.Close(); return(1); } } catch (Exception e) { MessageBox.Show("Sửa không thành công, vui lòng kiểm tra lại!"); return(0); } }