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