1 / 4

SQL-Server-Function

Learn how to create a table in the database

ankush21
Download Presentation

SQL-Server-Function

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. We are going to cover 2 functions over here. 1 - Bulk Data Import Using .Net SqlBulkCopy Class 2 - List Out Last Modified Procedures And Functions These functions will help you to configure and manger SQL server easily. 1 - Bulk Data Import Using .Net SqlBulkCopy Class (Original - https://theonetechnologies.com/blog/post/sql-server-bulk-data-import-using- net-sqlbulkcopy-class) Lets see how do we import bulk data in MySQL database? To deal with MySQL database from .Net, we need .Net connector. Download MySQL .Net Connector (ADO.NET driver for MySQL) from MySQL developer website. When you install this connector, it will give you DLLs required to deal with MySQL database from .Net code. Reference MySql.Data.dll in your .Net project, we'll be using MySqlBulkLoader class to import bulk data. However this class doesn't provide any direct way to load DataTable into database, but it provides way to bulk load .csv file. Step 1: Function to create .csv file from DataTable (you can skip this, if you already have csv file) public static void CreateCSVfile(DataTable dtable, string strFilePath) { StreamWriter sw = new StreamWriter(strFilePath, false); int icolcount = dtable.Columns.Count; foreach (DataRow drow in dtable.Rows) { for (int i = 0; i < icolcount; i++) { if (!Convert.IsDBNull(drow[i])) { sw.Write(drow[i].ToString()); } if (i < icolcount - 1) { sw.Write(","); } } sw.Write(sw.NewLine); } sw.Close(); sw.Dispose(); }

  2. Step 2: Import data into MySQL database private void ImportMySQL() { DataTable orderDetail = new DataTable("ItemDetail"); DataColumn c = new DataColumn(); orderDetail.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("value", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("length", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32"))); orderDetail.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal"))); orderDetail.Columns["total"].Expression = "value/(length*breadth)"; // always //Adding dummy entries DataRow dr = orderDetail.NewRow(); dr["ID"] = 1; dr["value"] = 50; dr["length"] = 5; dr["breadth"] = 8; orderDetail.Rows.Add(dr); dr = orderDetail.NewRow(); dr["ID"] = 2; dr["value"] = 60; dr["length"] = 15; dr["breadth"] = 18; orderDetail.Rows.Add(dr); //Adding dummy entries string connectMySQL = "Server=localhost;Database=test;Uid=username;Pwd=password;"; string strFile = "/TempFolder/MySQL" + DateTime.Now.Ticks.ToString() + ".csv"; //Create directory if not exist... Make sure directory has required rights.. if (!Directory.Exists(Server.MapPath("~/TempFolder/"))) Directory.CreateDirectory(Server.MapPath("~/TempFolder/")); //If file does not exist then create it and right data into it.. if (!File.Exists(Server.MapPath(strFile))) { FileStream fs = new FileStream(Server.MapPath(strFile), FileMode.Create, FileAccess.Write); fs.Close();

  3. fs.Dispose(); } //Generate csv file from where data read CreateCSVfile(orderDetail, Server.MapPath(strFile)); using (MySqlConnection cn1 = new MySqlConnection(connectMySQL)) { cn1.Open(); MySqlBulkLoader bcp1 = new MySqlBulkLoader(cn1); bcp1.TableName = "productorder"; //Create ProductOrder table into MYSQL database... bcp1.FieldTerminator = ","; bcp1.LineTerminator = "\r\n"; bcp1.FileName = Server.MapPath(strFile); bcp1.NumberOfLinesToSkip = 0; bcp1.Load(); //Once data write into db then delete file.. try { File.Delete(Server.MapPath(strFile)); } catch (Exception ex) { string str = ex.Message; } } } 2 - List Out Last Modified Procedures And Functions (Original - https://theonetechnologies.com/blog/post/sql-server-list-out-last-modified- procedures-and-functions) SELECT name, create_date, modify_date,type FROM sys.objects WHERE type IN ('FN','V','P','U','TF') AND DATEDIFF(D,modify_date, GETDATE()) < 6 ORDER BY modify_date Here are different types used in IN clause in WHERE condition: FN – Scalar valued function V – View P – Stored Procedure U – Table TF – Table valued function AND DATEDIFF(D,modify_date, GETDATE()) < 6 – It will show the objects modified in last 6 days

More Related