public DataSet Update() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); NpgsqlParameter[] param = new NpgsqlParameter[6]; DataSet ds = new DataSet(); param[0] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[0].Value = id; param[1] = new NpgsqlParameter("@in_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[1].Value = aname; param[2] = new NpgsqlParameter("@in_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[2].Value = ename; param[3] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[3].Value = user_id; param[4] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2)); param[4].Value = company_id; param[5] = new NpgsqlParameter("@in_stop", NpgsqlTypes.NpgsqlDbType.Boolean); param[5].Value = stop; ds = dal.ExecuteAndRetriveDataSet("scl.fnc_section_update", param); ds.Tables[0].TableName = "tbl_section"; return(ds); }
public DataSet Insert() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); NpgsqlParameter[] param = new NpgsqlParameter[6]; DataSet ds = new DataSet(); param[0] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[0].Value = id; param[1] = new NpgsqlParameter("@in_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[1].Value = aname; param[2] = new NpgsqlParameter("@in_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[2].Value = ename; param[3] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[3].Value = user_id; param[4] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2)); param[4].Value = company_id; param[5] = new NpgsqlParameter("@in_city_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[5].Value = city_id; ds = dal.ExecuteAndRetriveDataSet("grl.fnc_areas_insert", param);; ds.Tables[0].TableName = "tbl_areas"; return(ds); }
public DataSet Select() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); DataSet ds = new DataSet(); ds = dal.ExecuteAndRetriveDataSet("Select * From scl.tbl_section order by sequ;"); ds.Tables[0].TableName = "tbl_section"; return(ds); }
public DataSet Select() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); DataSet ds = new DataSet(); ds = dal.ExecuteAndRetriveDataSet("Select * From grl.tbl_areas order by sequ;" + "Select id,aname,ename From grl.tbl_cities order by sequ;"); ds.Tables[0].TableName = "tbl_areas"; ds.Tables[1].TableName = "tbl_cities"; return(ds); }
void GetAllDatabase() { fld_DAL.cls_dal dal = new fld_DAL.cls_dal("postgres"); com_database.Items.Clear(); foreach (DataRow r in dal.ExecuteAndRetriveDataSet("SELECT * FROM pg_database WHERE datistemplate = false;").Tables[0].Rows) { com_database.Items.Add(r["datname"].ToString()); } com_database.Items.Remove("postgres"); com_database.Text = Properties.Settings.Default.Database; }
public DataSet Select() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); DataSet ds = new DataSet(); string select = ""; switch (selecttype) { case "Login": select = "SELECT * FROM scl.tbl_parents WHERE id = '0';"; break; case "Select_All": select = "SELECT * FROM scl.tbl_parents WHERE company_id = '" + company_id + "';"; break; case "Select": select = "SELECT * FROM scl.tbl_parents WHERE id = '" + id + "' AND company_id = '" + company_id + "';"; break; case "Search": select = "select * from scl.tbl_parents where(f_parent_id = '" + f_parent_id + "'or id like '%" + id + "' or f_mobile1 = '" + f_mobile1 + "' or f_aname like '%" + f_aname + "%' or ff_aname like '%" + ff_aname + " %'or m_parent_id = '" + m_parent_id + "' or m_mobile1 = '" + m_mobile1 + "' or m_aname like '%" + m_aname + "%' or mf_aname like '%" + mf_aname + "%'or f_ename like '" + f_ename + "%' or ff_ename like '%" + ff_ename + "%' or m_ename like '%" + m_ename + "%' or mf_ename like '%" + mf_ename + "%')and company_id = '" + company_id + "';"; break; case "First": select = "SELECT * FROM scl.tbl_parents WHERE company_id = '" + company_id + "' ORDER BY ctid ASC LIMIT 1;"; break; case "Prev": select = "SELECT * FROM scl.tbl_parents WHERE id = (SELECT id FROM scl.tbl_parents WHERE sequ < (SELECT sequ FROM scl.tbl_parents WHERE id = '" + id + "' AND company_id = '" + company_id + "' LIMIT 1) AND company_id = '" + company_id + "' ORDER BY ctid Desc LIMIT 1) AND company_id = '" + company_id + "';"; break; case "Next": select = "SELECT * FROM scl.tbl_parents WHERE id = (SELECT id FROM scl.tbl_parents WHERE sequ > (SELECT sequ FROM scl.tbl_parents WHERE id = '" + id + "' AND company_id = '" + company_id + "' LIMIT 1) AND company_id = '" + company_id + "' ORDER BY ctid ASC LIMIT 1) AND company_id = '" + company_id + "';"; break; case "Last": select = "SELECT * FROM scl.tbl_parents WHERE company_id = '" + company_id + "' ORDER BY ctid Desc LIMIT 1;"; break; } ds = dal.ExecuteAndRetriveDataSet(select + "Select * From grl.tbl_nationality order by ctid;" + "Select * From grl.tbl_religion order by ctid;" ); ds.Tables[0].TableName = "tbl_Parents"; ds.Tables[1].TableName = "tbl_nationality"; ds.Tables[2].TableName = "tbl_religion"; return(ds); }
public DataSet Delete() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); NpgsqlParameter[] param = new NpgsqlParameter[3]; DataSet ds = new DataSet(); param[0] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, 4); param[0].Value = id; param[1] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[1].Value = user_id; param[2] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2)); param[2].Value = company_id; ds = dal.ExecuteAndRetriveDataSet("grl.fnc_nationality_delete", param); ds.Tables[0].TableName = "tbl_nationality"; return(ds); }
public DataSet Update() { pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal(); NpgsqlParameter[] param = new NpgsqlParameter[36]; DataSet ds = new DataSet(); // father parameters param[0] = new NpgsqlParameter("@in_f_parent_id", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[0].Value = f_parent_id; param[1] = new NpgsqlParameter("@in_f_enddate_id", NpgsqlTypes.NpgsqlDbType.Timestamp); param[1].Value = f_enddate_id; param[2] = new NpgsqlParameter("@in_f_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[2].Value = f_aname; param[3] = new NpgsqlParameter("@in_f_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[3].Value = f_ename; param[4] = new NpgsqlParameter("@in_ff_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[4].Value = ff_aname; param[5] = new NpgsqlParameter("@in_ff_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[5].Value = ff_ename; param[6] = new NpgsqlParameter("@in_f_religion", NpgsqlTypes.NpgsqlDbType.Integer); param[6].Value = f_religion; param[7] = new NpgsqlParameter("@in_f_country", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[7].Value = f_country; param[8] = new NpgsqlParameter("@in_f_hadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[8].Value = f_haddress; param[9] = new NpgsqlParameter("@in_f_wadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[9].Value = f_waddress; param[10] = new NpgsqlParameter("@in_f_mobile1", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[10].Value = f_mobile1; param[11] = new NpgsqlParameter("@in_f_hphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[11].Value = m_hphone; param[12] = new NpgsqlParameter("@in_f_wphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[12].Value = f_wphone; param[13] = new NpgsqlParameter("@in_f_acc", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[13].Value = m_acc; param[14] = new NpgsqlParameter("@in_f_mail", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[14].Value = m_mail; // mother parameters param[15] = new NpgsqlParameter("@in_m_parent_id", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[15].Value = f_parent_id; param[16] = new NpgsqlParameter("@in_m_enddate_id", NpgsqlTypes.NpgsqlDbType.Timestamp); param[16].Value = f_enddate_id; param[17] = new NpgsqlParameter("@in_m_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[17].Value = f_aname; param[18] = new NpgsqlParameter("@in_m_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[18].Value = f_ename; param[19] = new NpgsqlParameter("@in_mf_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[19].Value = ff_aname; param[20] = new NpgsqlParameter("@in_mf_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[20].Value = ff_ename; param[21] = new NpgsqlParameter("@in_m_religion", NpgsqlTypes.NpgsqlDbType.Integer); param[21].Value = f_religion; param[22] = new NpgsqlParameter("@in_m_country", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[22].Value = f_country; param[23] = new NpgsqlParameter("@in_m_hadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[23].Value = f_haddress; param[24] = new NpgsqlParameter("@in_m_wadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[24].Value = f_waddress; param[25] = new NpgsqlParameter("@in_m_mobile1", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[25].Value = f_mobile1; param[26] = new NpgsqlParameter("@in_m_hphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[26].Value = m_hphone; param[27] = new NpgsqlParameter("@in_m_wphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20)); param[27].Value = f_wphone; param[28] = new NpgsqlParameter("@in_m_acc", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[28].Value = m_acc; param[29] = new NpgsqlParameter("@in_m_mail", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[29].Value = m_mail; // other parameters param[30] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, (50)); param[30].Value = id; param[31] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4)); param[31].Value = user_id; param[32] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2)); param[32].Value = company_id; param[33] = new NpgsqlParameter("@in_stop", NpgsqlTypes.NpgsqlDbType.Boolean); param[33].Value = stop; param[34] = new NpgsqlParameter("@in_notes", NpgsqlTypes.NpgsqlDbType.Text); param[34].Value = notes; param[35] = new NpgsqlParameter("@in_branch_id", NpgsqlTypes.NpgsqlDbType.Varchar, (3)); param[35].Value = branch_id; ds = dal.ExecuteAndRetriveDataSet("scl.fnc_parents_update", param);; ds.Tables[0].TableName = "tbl_parents"; return(ds); }
private void btn_AddDatabase_Click(object sender, EventArgs e) { #region Validation if (txt_databasename.Text.Trim() == "") { MessageBox.Show("يجب إدخال أسم لقاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txt_databasename.Focus(); return; } #endregion if (rad_EmptyDatabase.Checked) { #region Validation if (txt_databasename.Text.Trim() == "") { MessageBox.Show("يجب إدخال أسم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txt_databasename.Focus(); return; } else { txt_databasename.Text = txt_databasename.Text.Trim().ToLower(); } if (txt_UserName.Text.Trim() == "") { MessageBox.Show("يجب إدخال أسم مستخدم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txt_UserName.Focus(); return; } if (txt_Password.Text.Trim() == "") { MessageBox.Show("يجب إدخال كلمة مرور لمستخدم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txt_Password.Focus(); return; } if (txt_ConfirmPassword.Text.Trim() == "") { MessageBox.Show("يجب إدخال تأكيد كلمة مرور لمستخدم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txt_ConfirmPassword.Focus(); return; } if (txt_Password.Text.Trim() != txt_ConfirmPassword.Text.Trim()) { MessageBox.Show("كلمة المرور غير متطابقة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); txt_ConfirmPassword.Focus(); return; } #endregion try { btn_AddDatabase.Text = "Please Wait ..."; btn_AddDatabase.Enabled = false; string script = "CREATE DATABASE " + txt_databasename.Text.Trim() + ";"; fld_DAL.cls_dal dal1 = new fld_DAL.cls_dal("postgres"); dal1.ExecuteAndRetriveDataSet(script); script = Properties.Resources.CreateNewEmptyDatabase; fld_DAL.cls_dal dal2 = new fld_DAL.cls_dal(txt_databasename.Text.Trim()); dal2.ExecuteAndRetriveDataSet(script); script = "INSERT INTO grl.tbl_users(id, sequ, aname, ename, password, gender_id, company_id, branch_id, creationtime, createuser_id)" + "VALUES('0001', 1, '" + txt_UserName.Text.Trim() + "', '" + txt_UserName.Text.Trim() + "', '" + txt_Password.Text.Trim() + "', 1, '01', '001', '" + DateTime.Now.ToString("MM/dd/yyyy") + "', '0001');\r\n"; script += Properties.Resources.InsertMainDataToNewDatabase; fld_DAL.cls_dal dal3 = new fld_DAL.cls_dal(txt_databasename.Text.Trim()); dal3.ExecuteAndRetriveDataSet(script); MessageBox.Show("تم اضافة قاعدة البيانات بنجاح", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Information); txt_databasename.Focus(); Close(); } catch (Exception ex) { btn_AddDatabase.Text = "OK"; btn_AddDatabase.Enabled = true; MessageBox.Show(ex.Message, "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Error); throw; } } else if (rad_CopyDatabase.Checked) { #region Validation if (com_CopyDatabase.Text == "") { MessageBox.Show("يجب تحديد قاعدة بيانات للنسخ منها", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); com_CopyDatabase.DroppedDown = true; return; } #endregion try { btn_AddDatabase.Text = "Please Wait ..."; btn_AddDatabase.Enabled = false; string script = "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity " + "WHERE pg_stat_activity.datname = 'postgres' AND pid<> pg_backend_pid();"; script += "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity " + "WHERE pg_stat_activity.datname = '" + com_CopyDatabase.Text + "' AND pid<> pg_backend_pid();"; fld_DAL.cls_dal dal1 = new fld_DAL.cls_dal("postgres"); dal1.ExecuteAndRetriveDataSet(script); script = "CREATE DATABASE " + txt_databasename.Text.Trim() + " WITH TEMPLATE " + com_CopyDatabase.Text + " OWNER postgres;"; fld_DAL.cls_dal dal2 = new fld_DAL.cls_dal("postgres"); dal2.ExecuteAndRetriveDataSet(script); MessageBox.Show("تم اضافة قاعدة البيانات بنجاح", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Information); txt_databasename.Focus(); Close(); } catch (Exception ex) { btn_AddDatabase.Text = "OK"; btn_AddDatabase.Enabled = true; MessageBox.Show(ex.Message, "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Error); throw; } } }