Just include the source file in your program and call the functions. Using ( SqlConnection connection = new SqlConnection ( connectionString ) ) SqlCommand command = CreateInsertCommand ( row ) Inserts the DataRow for the connection, returning the identity public static object InsertDataRow ( DataRow row, string connectionString ) InsertParameter ( command, parameterName, String parameterName = " + column.ColumnName SqlCommand command = new SqlCommand ( sql ) Ĭommand.CommandType = Creates a SqlCommand for inserting a DataRow public static SqlCommand CreateInsertCommand ( DataRow row ) Parameter.SourceVersion = DataRowVersion.Current Parameter.Direction = ParameterDirection.Input SqlParameter parameter = new SqlParameter ( parameterName, value ) Creates a SqlParameter and adds it to the command public static void InsertParameter ( SqlCommand command, Sql.Append ( " SELECT CAST(scope_identity() AS " ) StringBuilder values = new StringBuilder ( " VALUES (" ) StringBuilder sql = new StringBuilder ( " INSERT INTO " + table.TableName + " (" ) Assumes autoincrement is identity (optional) public static string BuildInsertSQL ( DataTable table ) Returns a string containing all the fields in the table public static string BuildAllFieldsSQL ( DataTable table )įoreach ( DataColumn column in table.Columns ) Given the DataRow and a connection string, creates the SqlCommand as above and executes it, returning the identity of the record.įor example, if the Dataset Sesigner has defined a row like: public static object InsertDataRow ( DataRow row, string connectionString ).Given a DataRow, creates an instance of SqlCommand to insert the data into the DataSet. public static SqlCommand CreateInsertCommand ( DataRow row ).(Note that in this example, CustomerID is an Identity so it isn't included in the string.) E.g.: INSERT INTO tableName ( CustomerName.) VALUES SELECT CAST(scope_identity() AS int ). Returns an INSERT command with an optional SELECT CAST statement to get the SCOPE_IDENTITY if required. public static string BuildInsertSQL ( DataTable table ).Returns a list of all the columns in the DataTable in SQL format which can be used in a SELECT command etc. public static string BuildAllFieldsSQL ( DataTable table ).The file GenerateSQL.cs contains the code for a static class GenerateSQL, which has the following functions: Note that this has only been developed for simple functions, but the basics are generally applicable for other applications. I actually developed these functions because I needed to INSERT rows into a database and get the value of the Identity column on the fly as I tab through a DataGridView adding new rows. The code presented here does this by using functions in the Designer code to generate such strings. You would also like these lists to be automatically updated whenever a change is made to the database. Sometimes, however, you need additional SQL commands that contain a complete list of all the fields in the DataSet. Other commands can be added by going into the DataSet Designer and adding commands via the Add SQL Wizard. When you use TableAdapters in C#, VS generates INSERT, SELECT, and UPDATE etc. Download demo project and source - 25.4 KB.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |