Click here to Skip to main content
15,893,663 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone,
In my application I have a dataset, which has four tables. By taking the data from each of these tables, I have to fill another table in sql server.
Kindly help me with the sample code (insert query).

Thanks in advance...
Posted

C#
DataSet ds = new DataSet(); // Assign your DataSet object here
Dictionary<string, object> parameters;
List<string> sqlQuery = new List<string>();
// Write your SQL Query or Procedure Name in this list aganist each table.
// For example:
// For Table 1 the Query is: INSERT INTO Emp(FName, LName, Sal) VALUES(@FName, @LName, @Sal)
// For Table 2 the Query is: INSERT INTO Stu(FName, LName, Marks) VALUES(@FName, @LName, @Marks)
// Then you should add like following way:
sqlQuery.Add("INSERT INTO Emp(FName, LName, Sal) VALUES(@FName, @LName, @Sal)");
sqlQuery.Add("INSERT INTO Stu(FName, LName, Marks) VALUES(@FName, @LName, @Marks)");
// NOTE: you should add the query in the same order as the tables are in the DataSet
// That Means You have to follow the order (during adding sql query in the list) of adding DataTables in the DataSet respectively.
for(int i=0; i<ds.Tables.Count; i++)
{
	DataTable dt = ds.Tables[i];
	parameters = new Dictionary<string, object>();
	foreach (DataRow dr in dt.Rows)
	{
		for (int j = 0; j < dt.Columns.Count; j++)
			parameters.Add("@" + dt.Columns[j].ColumnName, dr[j]);
	}
	InsertUpdateDelete(sqlQuery[i], parameters, false);
}

/// <summary>
/// Insert, Update and Delete in the database through this method
/// </summary>
/// <param name="sql">The SQL Query or the name of the Stored Procedure</param>
/// <param name="parameters">The values which you have to insert, update, or delete</param>
/// <param name="isProcedure">If the first parameter "sql" is any name of the stored procedure then it must be true</param>
/// <returns>True for successful execution, otherwise False</returns>
public bool InsertUpdateDelete(string sql, Dictionary<string, object> parameters, bool isProcedure)
{
	using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
	{
		sqlConnection.Open();

		using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
		{
			if (isProcedure) sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
			else sqlCommand.CommandType = System.Data.CommandType.Text;

			// Adding parameters using Dictionary...
			foreach (KeyValuePair<string, object> parameter in parameters)
				sqlCommand.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
			if (sqlCommand.ExecuteNonQuery() > 0) return true;
			else return false;
		}
	}
}
 
Share this answer
 
v3
Comments
Member 11743415 9-Jun-15 11:34am    
i want code to insert dictionary<string,list<string>> values into sql table
neha dalakoti 6-Dec-17 12:19pm    
I tried this code but it works fine for one row but as its done with the first row and it goes to the second row it founds a key with the same name existing in the parameters and throws an exception.how to solve that?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900