المساعد الشخصي الرقمي

مشاهدة النسخة كاملة : database in sql



C# Programming
03-27-2009, 06:50 PM
hi
This is my code in which i upload a excel file using file dialog box and store it in to the sql server as table name emp here i creat only one table but problem is that when i run the program secound time all the values again store in same table
so my question is that is possible to create a table at run time in data base NEED HELP or any logic regaring that.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace openfi
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog FD = new OpenFileDialog();
FD.Filter = "Excel|*.xls";
if (FD.ShowDialog() == DialogResult.OK)
{
textBox1.Text = " " + FD.FileName;
}
string filename = textBox1.Text;
String connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [sheet1$]", connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
System.Data.DataTable dataTable = dataSet.Tables[0];
dataGridView1.DataSource = dataSet.Tables[0];
// textBox1.Text = "";
MessageBox.Show(dataGridView1.RowCount.ToString());
connection.Close();
}

private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog FD = new OpenFileDialog();
FD.Filter = "Excel|*.xls";
if (FD.ShowDialog() == DialogResult.OK)
{
textBox2.Text = " " + FD.FileName;
}
string filename = textBox2.Text;
String connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [sheet1$]", connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = selectCommand;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
System.Data.DataTable dataTable = dataSet.Tables[0];
dataGridView2.DataSource = dataSet.Tables[0];
// textBox1.Text = "";
MessageBox.Show(dataGridView2.RowCount.ToString());
connection.Close();

}

private void button3_Click(object sender, EventArgs e)
{
this.Close();
}

private void button4_Click(object sender, EventArgs e)
{

string constr="data source=MYSERVER;initial catalog=mo;integrated security=SSPI";


// string constr = "Datasource=Myserver;Initialcatlog=mo;Integrated security=SSPI";
SqlConnection sqcon = new SqlConnection(constr);
SqlCommand scmd = new SqlCommand();
sqcon.Open();
int i = 1;

while (i < (dataGridView1.Rows.Count)-2)

{
string istr = "insert into emp(Personnel_Area,EE_Grp,Employee_Group,ES_Grp)values('" + Convert.ToString(dataGridView1.Rows[i].Cells[0].Value.ToString()) + "','" + Convert.ToString(dataGridView1.Rows[i].Cells[1].Value.ToString()) + "','" + Convert.ToString(dataGridView1.Rows[i].Cells[2].Value.ToString()) + "','" + Convert.ToString(dataGridView1.Rows[i].Cells[3].Value.ToString()) + "')";
MessageBox.Show(istr);
scmd.CommandText = istr;
scmd.Connection = sqcon;
int r;
r = scmd.ExecuteNonQuery();
if (r > 0)
// MessageBox.Show("Record Saved");
i++;
}
if (i > 1)
{
MessageBox.Show("Records saved");
}



}
}
}