strSql = "SELECT * FROM Table1";
objConn = new SqlConnection(strConn);
objConn.Open();
SqlDataAdapter da = new SqlDataAdapter(strSql, objConn);
DataSet ds = new DataSet();
da.Fill(ds, "T_Table1");
objConn.Close();
objConn.Dispose();
Tag : .NET, Web (ASP.NET), C#, VS 2010 (.NET 4.x)
Date :
2013-11-25 17:14:00
By :
isEmpty
View :
1134
Reply :
2
No. 1
Guest
จากโค้ดด้านบน
ถ้าใช้ dataadapter ก็ไม่จำเป็นต้องเปิด connection
ส่วนคำถาม
Code (C#)
private DataTable GetData(string queryString)
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.CommandType = CommandType.Text;
command.CommandText = queryString;
adapter.Fill(dt);
}
return dt;
}
private DataTable GetData(string queryString, object[] parameterValues)
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.CommandType = CommandType.Text;
command.CommandText = queryString;
string[] parameterNames = queryString.Split((" ,;()").ToCharArray()).Where(n => n.StartsWith("@")).ToArray();
if (parameterNames.Length > 0)
{
var parameters = parameterNames.Zip(parameterValues, (n, v) => new { Name = n, Value = v });
foreach (var p in parameters)
{
command.Parameters.AddWithValue(p.Name, p.Value);
}
}
adapter.Fill(dt);
}
return dt;
}
Code (C#)
\\ ex.1
dataGridView1.DataSource = GetData("SELECT * FROM Employees;");
\\ ex.2
dataGridView2.DataSource = GetData("SELECT * FROM Employees WHERE EmployeeID > @EmployeeID AND BirthDate BETWEEN @StartDate AND @EndDate;",
new object[] { 1, new DateTime(1957, 1, 1), new DateTime(1962, 1, 1) });
Date :
2013-11-26 09:31:03
By :
ห้ามตอบเกินวันละ 2 กระทู้
No. 2
Guest
เขียนแบบมี parameters ใหม่ดีกว่า
Code (C#)
private DataTable GetData(string queryString, Dictionary<string, object> parameters)
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
command.CommandType = CommandType.Text;
command.CommandText = queryString;
command.Parameters.AddRange(parameters.Select(p => new SqlParameter(p.Key, p.Value)).ToArray());
adapter.Fill(dt);
}
return dt;
}
Code (C#)
dataGridView2.DataSource = GetData("SELECT * FROM Employees WHERE EmployeeID > @EmployeeID AND BirthDate BETWEEN @StartDate AND @EndDate;",
new Dictionary<string, object>()
{
{ "@EmployeeID", 1 },
{ "@StartDate", new DateTime(1957, 1, 1) },
{ "@EndDate", new DateTime(1962, 1, 1) }
});
Load balance : Server 03
