Here is my code to import excel file into sql database. Both source and target have same structure. I get this error when I run it. "Coulumn,parameter or variable @source cannot find datatype dbo.tStudent "
public static void ImportToSql(string excelfilepath)
{
//declare variables - edit these based on your particular situation
string ssqltable = "tStudent";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
string myexceldataquery = "select id,student,rollno,course from [sheet1$]";
try
{
string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
string ssqlconnectionstring = "Data Source=DELL\\SQLSERVER1;Trusted_Connection=True;DATABASE=Test;CONNECTION RESET=FALSE";
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
//series of commands to bulk copy data from the excel file into our sql table
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
//while (dr.Read())
//{
// //bulkcopy.WriteToServer(dr);
//}
SqlCommand sqlcmd = new SqlCommand(@"MERGE tStudent AS target
USING (select ID, STUDENT , ROLLNO from @source) as source
ON (source.ID = target.ID)
WHEN MATCHED THEN
UPDATE SET Student = source.Student,
ROLLNO = source.ROLLNO
WHEN NOT MATCHED THEN
INSERT (ID, STUDENT , ROLLNO )
VALUES (source.id, source.Student);", sqlconn);
SqlParameter param;
param = sqlcmd.Parameters.AddWithValue("@source", dr);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.tStudent";
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
while (dr.Read())
{
}
oledbconn.Close();
Console.WriteLine(".xlsx file imported succssessfully into database.");
}
Help me with this as I am stuck for a long time.
Here is my code to import excel file into sql database. Both source and target have same structure. I get this error when I run it. "Coulumn,parameter or variable @source cannot find datatype dbo.tStudent "
public static void ImportToSql(string excelfilepath)
{
//declare variables - edit these based on your particular situation
string ssqltable = "tStudent";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
string myexceldataquery = "select id,student,rollno,course from [sheet1$]";
try
{
string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
string ssqlconnectionstring = "Data Source=DELL\\SQLSERVER1;Trusted_Connection=True;DATABASE=Test;CONNECTION RESET=FALSE";
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
//series of commands to bulk copy data from the excel file into our sql table
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
//while (dr.Read())
//{
// //bulkcopy.WriteToServer(dr);
//}
SqlCommand sqlcmd = new SqlCommand(@"MERGE tStudent AS target
USING (select ID, STUDENT , ROLLNO from @source) as source
ON (source.ID = target.ID)
WHEN MATCHED THEN
UPDATE SET Student = source.Student,
ROLLNO = source.ROLLNO
WHEN NOT MATCHED THEN
INSERT (ID, STUDENT , ROLLNO )
VALUES (source.id, source.Student);", sqlconn);
SqlParameter param;
param = sqlcmd.Parameters.AddWithValue("@source", dr);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.tStudent";
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
while (dr.Read())
{
}
oledbconn.Close();
Console.WriteLine(".xlsx file imported succssessfully into database.");
}
Help me with this as I am stuck for a long time.
0 commentaires:
Enregistrer un commentaire