I'm not on my dev machine so I can't write a sample using stored procedures. But here's one using dynamic query:
private string GenerateDynamicQuery(string baseSql, StringCollection sc){
StringBuilder sb = new StringBuilder(string.Empty);
foreach (string item in sc){
sb.AppendFormat("{0}('{1}'); ", baseSql, item);
}
return sb.ToString();
}
private void InsertRecords(StringCollection sc){
const string sqlStatement = "INSERT INTO Employee (Name) VALUES";
string dynamicQuery = GenerateDynamicQuery(sqlStatement,sc);
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(dynamicQuery,connection)){
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
BindGrid(sc);
}
private void BindGrid(StringCollection sc){
StringBuilder sb = new StringBuilder(string.Empty);
foreach (string item in sc){
sb.AppendFormat("'{0}',", item);
}
string param = sb.ToString().TrimEnd(',');
string sqlStatement = string.Format("SELECT * FROM Employee WHERE Name IN ({0});",param);
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(sqlStatement,connection)){
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
if (dt.Rows.Count > 0) {
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
StringCollection sc = new StringCollection();
foreach (ListItem item in ListBox1.Items)
{
if (item.Selected){
sc.Add(item.Text);
}
}
InsertRecords(sc);
}
Here's another solution with parameterize query to "prevent" SQL Injection:
private void InsertRecords(StringCollection sc){
const string sqlStatement = "INSERT INTO Employee (Name) VALUES (@param1)";
foreach (string item in sc){
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(sqlStatement ,connection)){
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@param1", item)
cmd.ExecuteNonQuery();
}
}
}
BindGrid(sc);
}
private void BindGrid(StringCollection sc){
string[] strArray = new string[sc.Count];
sc.CopyTo(strArray,0);
var parms = strArray.Select((s, i) => "@param1" + i.ToString()).ToArray();
var inclause = string.Join(",", parms);
string sqlStatement = "SELECT * FROM Employee WHERE Name IN ({0})";
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(string.Format(sqlStatement, inclause),connection)){
for (var i = 0; i < valuearray.Length; i++)
{
cmd.Parameters.AddWithValue(parms[i], strArray[i]);
}
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
if (dt.Rows.Count > 0) {
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
StringCollection sc = new StringCollection();
foreach (ListItem item in ListBox1.Items)
{
if (item.Selected){
sc.Add(item.Text);
}
}
InsertRecords(sc);
}
PS:Never tested on the paramertize solution in actual but that should give you some idea on how to do it.