How to creat a sql table giving specified column names and types

odbc_Cmnd = new OdbcCommand("INSERT INTO stos_day(number_id, n_zadan,vid_ppl,dou,n_dou,n_ud, movd,rovd,data_p,tip_ppl, prim) VALUES (?,?,?,?,?,?,?,?,?,?,?)", odbc_DBConn);

odbc_Cmnd.Parameters.Add("number_id", OdbcType.Int);
odbc_Cmnd.Parameters.Add("n_zadan", OdbcType.Char);
odbc_Cmnd.Parameters.Add("vid_ppl", OdbcType.Char);
odbc_Cmnd.Parameters.Add("dou", OdbcType.Char);
odbc_Cmnd.Parameters.Add("n_dou", OdbcType.Char);
odbc_Cmnd.Parameters.Add("n_ud", OdbcType.Char);
odbc_Cmnd.Parameters.Add("movd", OdbcType.Char);
odbc_Cmnd.Parameters.Add("rovd", OdbcType.Char);
odbc_Cmnd.Parameters.Add("data_p", OdbcType.Date);
odbc_Cmnd.Parameters.Add("tip_ppl", OdbcType.Int);
odbc_Cmnd.Parameters.Add("prim", OdbcType.Text);

Here is created dbf table I want the same for SQL table

Answer

You can find in these link step by step to setup SQL connection, Create database, Insert values into tables

Create an SQL Server Database Using C#

Create a SQL Server Database dynamically in C#

Eg:

you have to set connection string

//declaring variables
string connetionString;
SqlConnection cnn;
//setting connection
connetionString = @"Data Source=WIN-50GP30FGO75;Initial Catalog=Demodb;User ID=sa;Password=demol23";

Assign connection & open connection

cnn = new SqlConnection(connetionString);
cnn.Open();
//do some actions
cnn.close

Code Explanation:-

• The first step is to create variables, which will be used to create the connection string and the connection to the SQL Server database.
• The next step is to create the connection string. The connecting string needs to be specified correctly for C# to understand the connection string. The connection string consists of the following parts :
  • Data Source – This is the name of the server on which the database resides. In our case, it resides on a machine called WIN- 50GP30FGO75.
  • The Initial Catalog is used to specify the name of the database
  • The UserID and Password are the credentials required to connect to the database.
• Next, we assign the connecting string to the variable cnn. The variable cnn, which is of type SqlConnection is used to establish the connection to the database.
• Next, we use the Open method of the cnn variable to open a connection to the database. We then just display a message to the user that the connection is established.
• Once the operation is completed successfully, we then close the connection to the database. It is always a good practice to close the connection to the database if nothing else is required to be done on the database.

Adding columns to DB

using (SqlConnection connection = new SqlConnection("Your connection string"))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.CommandText = string.Format("ALTER TABLE TABLE_NAME ADD colName1 colName2", "[variable_1]", "[variable_2]");
                    command.Connection = connection;
                    command.ExecuteNonQuery();
                }
            }

Insert Into Database

//defining variables
SqlCommand command;
SqlDataAdapter adapter = new SqlDataAdapter();
String sql="";
//insert statement
sql = "Insert into demotb (col1, col_2) values(variable_1,variable_2)";
//define sql command
command = new SqlCommand(sql, cnn)
//Associate the insert command
adapter.InsertCommand = new SqlCommand(sql,conn);
adapter.InsertCommand.ExecuteNonQuery();
//close all objects and connection
command.Dispose();
cnn.Close();

Code Explanation:-

• The first step is to create the following variables

  • SQLCommand – This data type is used to define objects which are used to perform SQL operations against a database. This object will hold the SQL command which will run against our SQL Server database.
  • The DataAdapter object is used to perform specific SQL operations such as insert, delete and update commands.
  • We then define a string variable, which is SQL to hold our SQL command string.

• The next step is to actually define the SQL statement which will be used against our database. In our case, we are issuing an insert statement, which will insert the record of col_1=variable_1 and col_2=variable_2

• Next, we create the command object which is used to execute the SQL statement against the database. In the SQL command, you have to pass the connection object and the SQL string

• In our data adapter command, we now associate the insert SQL command to our adapter. We also then issue the ExecuteNonQuery method which is used to execute the Insert statement against our database. The ‘ExecuteNonQuery’ method is used in C# to issue any DML statements against the database. By DML statements, we mean the insert, delete, and update operation. In C# , if you want to issue any of these statements against a table, you need to use the ExecuteNonQuery method.

• We finally close all the objects related to our database operation.