DBAccess_SP
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace ADO.SP
{
partial class dbAccessSP
{
private string connS = "";
public dbAccessSP()
{
connS = System.Configuration.ConfigurationManager.ConnectionStrings["Test"].ToString();
}
// PROPERTIES
private string sp_name ="";
public void SetName(string sp_name) {
this.sp_name = sp_name;
}
private DataTable para_db = new DataTable();
public void SP_Para(DataTable sp_para)
{
this.para_db = sp_para;
}
// UPDATE
public bool UPDATE_SP() {
string SPName = this.sp_name;
DataTable dt = this.para_db;
SqlConnection conn = new SqlConnection(connS);
SqlCommand cmd = new SqlCommand(SPName, conn);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < dt.Rows.Count; i++)
{
cmd.Parameters.AddWithValue("@"+dt.Rows[i][0].ToString (), dt.Rows[i][1].ToString ());
}
try
{
conn.Open();
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally {
conn.Close();
cmd.Dispose();
conn.Dispose();
}
}
// SELECT
//SELECT FROM READER
public DataTable SELECT_SP()
{
string SPName = this.sp_name;
SqlConnection conn = new SqlConnection(connS);
SqlCommand cmd = new SqlCommand(SPName, conn);
DataTable dt = this.para_db;
SqlDataReader rd = null;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < dt.Rows.Count; i++)
{
cmd.Parameters.AddWithValue("@" + dt.Rows[i][0].ToString(), dt.Rows[i][1].ToString());
}
try
{
conn.Open();
rd = cmd.ExecuteReader();
dt = GetReaderTable(rd.GetSchemaTable());
object[] array = new object[dt.Columns.Count];
while (rd.Read())
{
for (int i = 0; i < dt.Columns.Count; i++)
{
array[i] = rd.GetValue(i);
}
dt.Rows.Add(array);
}
return dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
finally
{
conn.Close();
rd.Dispose();
conn.Dispose();
dt.Dispose();
cmd.Dispose();
}
}
private DataTable GetReaderTable(DataTable pdt)
{
DataTable dt = new DataTable("info");
for (int i = 0; i < pdt.Rows.Count; i++)
{
dt.Columns.Add(pdt.Rows[i][0].ToString());
}
return dt;
}
public DataTable ParaHolder() {
DataTable dt = new DataTable();
dt.Columns.Add("para");
dt.Columns.Add("values");
return dt;
}
}
}
------------------------------------------------------------
Caller
dbAccessSP objdb = new dbAccessSP();
private void button1_Click(object sender, EventArgs e)
{
objdb.SetName("SPInsertStudent");
DataTable para = objdb.ParaHolder();
para.Rows.Add("name",txtName.Text);
para.Rows.Add("address",txtAddress.Text);
para.Rows.Add("tp",txtTP.Text);
para.Rows.Add("dob",dateTimePicker1.Value.ToShortDateString());
objdb.SP_Para(para);
if (objdb.UPDATE_SP()) {
loadINFo();
MessageBox.Show("success");
}
else
{
MessageBox.Show("not success5");
}
}
private void UpdateStudents_Load(object sender, EventArgs e)
{
loadINFo();
}
public void loadINFo() {
objdb.SetName("SPSelectStudents");
DataTable para = objdb.ParaHolder();
objdb.SP_Para(para);
dataGridView1.DataSource = objdb.SELECT_SP();
}
