#ExecuteNonQuery

[jhponestop]

ADO.NET is such an interface that is created to connect .NET applications to RDBMS systems

ADO.NET is a collection of managed libraries used by .NET applications for data source communication using a driver or provider: Enterprise applications handle a large amount of data. This data is primarily stored in relational databases, such as ado.net executenonquery return value Oracle, SQL Server, and Access and so on. These databases use Structured Query Language (SQL) for retrieval of…

[innercreationthing-blog]

How to delete rows using simple sql statement in C#

How to delete rows using simple sql statement in C#

As we have seen how to delete rows in a dataset  in C#.Net, there are simple ways to do the same using SQL command object. All we need is an sql statement and an Object to do the job.

con.open();Common.cmd = new SqlCommand("delete from purchasepart where billno=" + b1, Common.con); Common.cmd.ExecuteNonQuery();con.close();

cmd object configure the delete statement and call the ExecuteNonQuery()…

[knowledgewiki]

How to convert text file into SQL insert statement?

I would upload a text file, and it will convert every row into a SQL insert statement, so it can insert into the database.

As my tried, there are 3 problems here:

it will insert 5 times the values at once

all the No. should be skip

As the strSQL, I need to insert the arrayOutput one by one to insert them all

Here is my text file

No Subject Code Subject Name Student ID Student Name Student Email Lecturer Name Lecturer Email 1 BSOA011 COMPUTERIZED PAYROLL 1751234-BSOA WONG QI YI dev.ng@outlook.com LIM BEE HUI semd2019@gmail.com 2 SEMD008 COMPUTER NETWORKING 1851234-SEMD LIM FENG FENG dev.ng@outlook.com NG WAI KONG semd2019@gmail.com 3 ITIN015 DISASTER RECOVERY MANAGEMENT 1851234-ITIN HO DENG DENG dev.ng@outlook.com SZE CHEN NEE @ SOO CHEN NEE semd2019@gmail.com 4 ECOM004 WEB DEVELOPMENT SKILLS: WEB PROGRAMMING (PHP & MYSQL) 1851234-ECOM KONG JIA WEN dev.ng@outlook.com TAN MING MING semd2019@gmail.com 5 CGDD002 PRACTICAL ICT SKILLS 1951234-CGDD CHONG SIAO YU dev.ng@outlook.com NG WAI KONG semd2019@gmail.com

Here is my code

Private Sub ReadAndReplace() Dim file_contents As String = "" locationPath = Server.MapPath("~") & "File" & filename If File.Exists(locationPath) Then Dim reader As StreamReader = My.Computer.FileSystem.OpenTextFileReader(locationPath) reader.ReadLine() 'skip first line Do While reader.Peek() <> -1 file_contents += reader.ReadLine Dim arrayOutput As String() = Split(file_contents, vbTab) Dim errMessage As String = "" Dim strConn As String = "server=localhost;port=3306; user id=root;" _ & "password=;database=school;SslMode=none" Dim oDBCon As New MySqlConnection(strConn) Try oDBCon.Open() 'INSERT RECORD Dim strSQL As String = "INSERT INTO studentinfo(subject_code, subject_name, student_id, student_name, student_email, lecturer_name, lecturer_email)VALUES('" _ & arrayOutput(1) & "','" & arrayOutput(2) & "','" & arrayOutput(3) _ & "','" & arrayOutput(4) & "','" & arrayOutput(5) _ & "','" & arrayOutput(6) & "','" & arrayOutput(7) & "');" Dim oCommand2 As New MySqlCommand(strSQL, oDBCon) oCommand2.ExecuteNonQuery() Catch ex As Exception errMessage = ex.Message Finally oDBCon.Close() End Try Loop reader.Close() End If End Sub

my output

1 Answer

Notice this line:

file_contents += reader.ReadLine

It is adding to initial file_contents variable so when it is split to create arrayOutput the top 7 array elements are always the same.

Maybe try file_contents = reader.ReadLine so you only split one line per iteration.

Archive from: https://stackoverflow.com/questions/59015510/asp-vb-net-how-to-convert-text-file-into-sql-insert-statement

from https://knowledgewiki.org/how-to-convert-text-file-into-sql-insert-statement/

[siva3155]

300+ TOP ADO.NET Objective Questions and Answers

ADO.Net Multiple Choice Questions :-

1. What is the default value for the Method attribute in a ASP.NET server-side Form control A. GET B. POST C. SOAP D. SMTP Answer:- B 2. Which property allows ASP.NET controls to maintain their values when a page is posted to itself. A. EnableViewState B. MaintainValue C. SaveValue D. AutoPostBack Answer:- A 3. In a ASP.NET page, which is the best way to find if the contents of a TextBox has changed after the form containing the TextBox has been posted back to the server A. By comparing the old value with the new value B. By checking the NewValue property of the TextBox control C. By handling the TextBox control's TextChanged event D. By checking the HasChanged property of the TextBox Answer:- C 4. Which statement among the ones below is correct A. The Params and Forms collection of the HTTPRequest class are exactly same and theForms collection has been provided only for backward compatibility B. The HTTPRequest class does include the Params collection C. The HTTPRequest class does include the Forms collection D. The difference between the Params and Forms collection of the HTTPRequest class is that the Params collection also represents QueryStrings, ServerVariables, andCookies Answer:- D 5. Which statement among the ones below is correct a. If you use the server-side version of the tag, you cannot post the form to a different page. b. You can modify the Action attribute of the server-side version of the tag to post the form to a different page c. You have to set the Method and Action attributes of the server-side version of the tag to post the form to a different page d. You can use the SetAction(. method of the server-side version of the tag to post the form to a different page Answer:- A 6. If a form is posted to a different page, the you can retrieve the values of the form elements by a. Using the properties of the ASP.Net controls b. Using the GetElementByID(. method c. Using Request.Params d. The values cannot be retrieved Answer:- C 7. You are performing Form validations with Validation controls. You need to group all the errors at the top of the page and also show error messages besides each element. The messages besides each element need to be detailed and hence different from the messages at top of the page A. You can use the ValidationSummary Control to summarize the messages set in theErrorMessage property of the Validation controls. The messages set in the Text property of the Validation controls can be displayed besides each element. B. The error messages set in the SummaryText property of each Validation control havingisSummary=true will be automatically grouped at the top of the page and the messages set in the Text property can be displayed besides each element. C. The error messages in the Validation control can only be displayed in a popup D. The Validation controls cannot be used in the given scenario Answer:- A 8. How do view states work A. Using Cookies B. Using a hidden form field C. Using Session variables D. Using Application variables Answer:- B 9. You need to dynamically add a TextBox control to a page and display it at specific location A. Place a Label control at the required location in the page and add the control by manipulating the Text property of the Label control B. Place a PlaceHolder control at the required location in the page. Use the Add method of theControls collection of the PlaceHolder control to dynamically add the required control C. Build the HTML for the entire page in a String and insert the HTML code for required control in required place D. You cannot add a control to a page dynamically Answer:- B 10. You require to create an ASP.NET page with the functionality to allow a user to upload a file to the server A. You need to use the System.Web.Upload namespace B. You need to use a COM component to save the file on the server C. You need to use the SaveAs method of the HttpPostedFile class D. The ASP.Net application automatically loops through all the and saves the uploaded files to a virtual folder called "uploads" Answer:- C

ADO.NET MCQs 11. You need to programmatically add a user control without using the Register directive in your code A. Use AddUserControl B. Use cannot programmatically add a user control C. Use UserControl.Add D. Use LoadControl Answer:- D 12. You need to develop a business component myComp.dll in .Net and use it in your ASP.NET page a. You require to register the component using regsvr32.exe b. You require to register the component using TlbExp.exe c. You require to copy the component to the /bin directory in your applications root directory d. You require to copy the component to the /bin directory in your applications root directory and register the component using regsvr32.exe Answer:- C 13. Is it possible for multiple aspx pages to use the same code-behind file, if required A. Yes B. No Answer:- A 14. How is a ASP.NET presentation page associated with its code-behind A. The presentation page includes the code-behind file using #include B. The presentation page includes the code-behind file using C. The presentation page inherits from the code-behind D. The ASP.NET presentation page and the code-behind page have the same name and hence are automatically associated with each other Answer:- C 15. Which namespace would you use if you need to create an ASP.NET application with Microsoft SQL Server 2000 as the database. A. System.Data.SQLServer B. System.Data.SQLClient C. System.Data.OleDb D. System.Data.Microsoft Answer:- B 16. Which namespace would you use if you need to create an ASP.NET application with Oracle as the database. A. System.Data.SQLServer B. System.Data.SQLClient C. System.Data.OleDb D. System.Data.NonMS Answer:- C 17. Which statement among the following is correct while opening a connection using theSQLConnection class A. You cannot specify a connection string B. You need to specify a Provider parameter for the connection string C. You need to use a System DSN D. You cannot use a DSN Answer:- C 18. What method of the Command object would you use if you need to retrieve a single value (for example an aggregate value. from a database A. ExecuteScalar(. B. ExecuteReader(. C. ExecuteSingle(. D. ExecuteNonQuery(. Answer:- A 19. Which are the two things you need to take care of in your ASP.Net application to take advantage of connection pooling (select two. A. You need to set the Pooling property of the Connection object to True B. You need to use the same exact connection string whenever you open a database connection C. You need to call the ReleaseToPool(. method after the connection is closed D. You need to explicitly close the connection with the Close(. method Answer:- B,D 20. You have created a ASP.Net page Transaction by using A. If a transaction already exists, the page will execute within the context of the transaction. However it will not create a new transaction B. If a transaction already exists, the page will execute within the context of the transaction. If a transaction does not exist, it will create a new one C. Creates a new transaction for each request D. You cannot created a ASP.Net page Transaction by using Answer:- B 21. You need to retrieve only schema information about the columns contained in a database table without retrieving data. Which of the following is the best way to retrieve it A. Use the GetSchemaTable(. method of the DataReader in conjunction withCommandBehaviour.SchemaOnly B. Use the SchemaOnly property of the Command class C. Use the GetSchemaOnly(. method of the DataReader D. You cannot retrieve only schema information without retrieving data Answer:- A 23.What will be the values assigned to the two Label controls lblMessage1 & lblMessage2 respectively A. 2 & 3 B. 3 & 3 C. 3 & 4 D. 4 & 4 Answer:- C 23. You need to customize the display format of the DataList control A. Set the DisplayFormat property of the DataList control to Custom B. Set the CustomFormat property of the DataList control to True C. ItemTemplate will allow you to format the appearance of each DataList item D. The display format of the DataList control is predefined and cannot be customized Answer:- C 24. You have a DataList control containing a LinkButton. What would you do to raise theDeleteCommand event in the DataList by clicking on the LinkButton. A. Set CommandName="delete" in the LinkButton B. Set the OnDelete property of the DataList to the ID of the LinkButton C. A DataList control cannot raise an event in response to events raised by its child controls D. A DataList control cannot contain child controls Answer:- A 25. The Products table has a column named ProductName and a primary key column namedProductID. When a user selects an item in the DataList, you want to retrieve the value of theProductID column associated with it. A. In the DataList control set PrimaryKeyField="ProductID" B. In the DataList control set DataKeyField="ProductID" C. In the DataList control set KeyField="ProductID" D. In the DataList control set id="ProductID" Answer:- B 26. You need to enable paging for a DataGrid control A. You can use a DataReader B. You can use a DataTable C. You can use either the DataReader or DataTable D. A DataGrid does not support paging Answer:- B 27. Can you use a DataView to filter rows in a DataTable A. Yes, by setting the Filter property of the DataView B. Yes, by setting the RowFilter property of the DataView C. Yes, by using the SetFilter(. method of the DataView D. No, a DataView cannot be used to filter rows in a DataTable Answer:- B 28. Which statement is an accurate description of the code below (dtsDataset is a DataSet. Cache("myCachedDataSet". = dstDataset a. Adds a DataSet to the application's cache in the server's memory, automatically preserving it between page requests. b. Adds a DataSet to the cache in the client's memory, automatically preserving it between page requests. c. The Cache object cannot be used to cache a DataSet, it can only cache a DataTable d. The code block is syntactically incorrect and should actually be Set Cache("myCachedDataSet". = dstDataset Answer:- A 29. How can you load an XML file directly into a DataSet A. By setting the XML property of the DataSet class B. By using the GetXML(. method of the DataSet class C. By using the ReadXml(. method of the DataSet class D. You cannot load an XML file directly into a DataSet Answer:- C 30. Your site has been restructured and the paths of few pages have changed. Which method would you use to redirect users requesting for a pages using the old URL A. Create an ISAPI filter to do the above task B. Create an entry in the section of the Web.Config file C. Use the Application.Config file D. Handle the Application_BeginRequest event and use the RewritePath(. method Answer:- D 31. Which statement about the Web.Config file is the most accurate A. The Web.Config file can be only placed in the root of a Web Site to override settings in theMachine.Config file for all applications in a particular Web Site B. The Web.Config file can only be placed in the root of a particular virtual directory C. The Web.Config file can be placed in the root of the Web Site and the root of a virtual directory. The settings from the file in the virtual directory overrides the settings from the file in the Web Site D. The Web.Config file can be placed in the root of the Web Site and the root of a virtual directory and in any subdirectory of an application. The settings from a file at a particular level override the settings from the ones above it. Answer:- D 32. The settings in the Web.Config file are case-sensitive A. True B. False Answer:- 33. The settings in the Web.Config file can be configured to apply to A. An application B. An application or a particular directory C. An application or a particular directory or even an individual file D. The Web.Config file always applies to all pages in the current directory and its subdirectories Answer:- C 34. Is there a way to prevent configuration settings in a Web.Config file from being overridden by aWeb.Config file located below it A. Yes, you can use the allowOverride attribute in the tag to prevent configuration settings in a Web.Config file from being overridden by a Web.Config file located below it B. Yes, you can use the tag to prevent configuration settings in aWeb.Config file from being overridden by a Web.Config file located below it C. If a Web.Config file exist at a particular level then all settings in the Web.Config file located above it are always ignored D. You cannot have more than one Web.Config file in an ASP.NET application Answer:- A 35. If you have an ASP.NET application with cookieless sessions enabled, which statement among the A. You cannot use relative URL's with cookieless sessions B. You cannot use absolute URL's with cookieless sessions C. Cookieless sessions have no relation with URL's D. You cannot have cookieless sessions with an ASP.NET application Answer:- B ADO.Net Objective type Questions with Answers 36. You are part of a team that is using Visual Studio .NET to develop a Web application. You have placed a number of configuration settings for the application in the server's Machine.config file. You learn in a meeting that one of your co-workers has created a Web.config file to store these settings, though in comparing notes you realize that you and your co-worker have used different settings. Which of the following describes what will happen when you execute the application a. The application will run correctly and will use the settings in the Web.config file, ignoring theMachine.config settings b. The application will run correctly and will use the settings in the Machine.config file, ignoring the Web.config settings c. The application will not run correctly until you either remove the Web.config file or delete the settings from the Machine.config file d. The application will run correctly, using only the settings that are identical in the Web.configand Machine.config files e. The application will not run correctly until you modify either the Web.config file or theMachine.config file so that the application settings are identical Answer:- A 37. You are preparing to deploy an ASP.NET Web application. You are currently creating the deployment project. You have started the Web setup project, which is namedAccountManagerDeploy, and added the application output to the project, and now you need to set the directory on the Web server to which the application will be installed. Which of the following procedures should you use to do so (Choose two. Each correct answer presents part of the solution.. A. Set the VirtualDirectory property in the Properties window. B. Select the Web Application Folder in the File System Editor. C. Select the AccountManagerDeploy project in the Solution Explorer. D. Set the Folder property in the Properties window. E. Select the Primary output from AccountManagerDeploy icon in the Solution Explorer. Answer:- A, B 38. You are part of a team that is using Visual Studio .NET to develop a Web application. You are looking through the files in the application, and you see the following files: createmembership.aspx createmembership.aspx.vb Which two of the following statements most accurately describe these two files (Select two.. A. The createmembership.aspx.vb file is a Web form, which displays a user interface to the user B. The createmembership.aspx file is a code file, which contains code that interacts with the user interface in the Web form C. The createmembership.aspx file is an uncompiled file that will become createmembership.aspx.vb when it is compiled D. The createmembership.aspx.vb file is a code file, which contains code that interacts with the user interface in the Web form E. The createmembership.aspx file is a Web form, which displays a user interface to the user F. The createmembership.aspx.vb file is the compiled version of createmembership.aspx Answer:- D, E 39. You are using Visual Studio .NET to develop a Web application. You are beginning to debug your application, and you would like to store debug messages in the Windows event log as you proceed with testing. Which of the following will allow you to do so A. TextWriterTraceListener B. DefaultTraceListener C. EventLogTraceListener D. TraceSwitch Answer:- C 40. You are using Visual Studio .NET to develop a Web application for a large video rental store. The titles, as well as other information about the store's videotapes, laser disks, DVDs, and audio books, are stored in a SQL Server 2000 database. The store wants to allow visitors to its Web site to browse the list of titles sorted in various ways, such as by medium (VHS, DVD, etc.., genre, director, and so on. You have created a number of stored procedures to facilitate the different ways of presenting the data. Given an existing DataSet named dsVids and a Connection named conn, which of the following code examples illustrates the correct way to call the stored procedure named ListDVDTitles that returns the list of DVD titles A. Dim daDVDList as New SqlDataAdapter(. daDVDList.SelectCommand = New SqlCommand(. daDVDList.SelectCommand.Connection = conn daDVDList.SelectCommand.CommandText = "ListDVDTitles" daDVDList.SelectCommand.CommandType = CommandType.StoredProcedure daDVDList.Fill(dsVids, "Titles". B. Dim daDVDList as New SqlDataAdapter(. daDVDList.SelectCommand = New StoredProcedure(. daDVDList.SelectCommand.Connection = conn daDVDList.SelectCommand.CommandText = "ListDVDTitles" daDVDList.SelectCommand.CommandType = CommandType.SQLCommand daDVDList.Fill(dsVids, "Titles". C. Dim daDVDList as New SqlDataAdapter(. daDVDList.SelectCommand = New SqlCommand(. daDVDList.SelectCommand.Connection = "ListDVDTitles" daDVDList.SelectCommand.CommandType = CommandType.StoredProcedure daDVDList.Fill(dsVids, "Titles". D. Dim daDVDList as New SqlDataAdapter(. daDVDList.SelectCommand = New SqlCommand(. daDVDList.SelectCommand.CommandText = "ListDVDTitles" daDVDList.SelectCommand.CommandType = CommandType.StoredProcedure daDVDList.Fill(dsVids, "Titles". Answer:- A 42. You are creating a Web service that will include a Web method. You want the method to return its data to the caller as it is created and put into a serial stream, rather than waiting for all of the data to be prepared before returning it. Which of the following WebMethod attributes should you set to enable this 43. The XSLT processor copies the elements of the stylesheet until it finds a command in the format: A. SELECT ... FROM ... WHERE. B. {item, action}. C. {for-each select}. D. none. Answer:- B 44. What standard, protocol or language was generalized to become a standard protocol for sending messages of any type, using any protocol? A. SOAP B. SGML C. SQL D. ADO Answer: A 45. Which of the following statements is not true about XML Schemas: A. They are used to define the content and structure of data. B. They define a set of symbols and the relationships of those symbols. C. They are themselves XML documents. D. They have their own syntax. Answer:- D 46. The most popular way to materialize XML documents is to use: A. DTD. B. XSLT. C. HTML. D. SOAP. Answer:- B 47. With XML: A. views are not limited to one multi-valued path only. B. documents can automatically be generated from database data only. C. database data can automatically be extracted from XML documents only. D. With XML, all of the above are true. Answer:- D 48. To eliminate definition duplication, XML Schemas define: A. an intersection table. B. global elements. C. a normalized definition table. D. None of the above is correct. Answer:- B 48. What is not true about SOAP? A. SOAP originally meant Simple Object Access Protocol. B. SOAP was defined as an XML-based standard for providing remote procedure calls over the Internet. C. SOAP now is just a name, not an acronym D. SOAP was an early form of XML. Answer:- D 49. XML Schemas consist of: A. properties and methods. B. elements and attributes. C. structure and data. D. tables and relationships. Answer:- B 50. The expression FOR XML RAW tells SQL Server to: A. place the values of the columns as attributes in the resulting XML document. B. place the values of the columns into elements rather than attributes. C. place some columns into elements and others into attributes. D. None of the above is correct. Answer:- A 51. ADO.NET provides the ability to create and process in-memory databases called: A. views. B. relations. C. tables. D. datasets. Answer:- D 52. An XML component that defines the structure of a document is known as an A. DOCTYPE. B. DTD. C. #PCDATA. D. HTML Stylesheet. Answer:- B 53. HTML is an application of a more robust document markup language called: A. XHTML. B. XML. C. SGML. D. None of the above is correct. Answer:- C 54. XSLT processors evaluate each statement in the context of the match that has been made. That is, XSLT processors are: A. context oriented. B. procedural oriented. C. object oriented. D. relational oriented. Answer:- A 55. The DTD begins with the word: A. #PCDATA. B. XML. C. DOCTYPE. D. HTTPS. Answer:- C 56. What is not true about XML? A. Web page display is the most important application of XML. B. With XML, there is a clear separation between document structure, content and materialization. C. XML is more powerful than HTML. D. XML documents have two sections. Answer:- A 57. What is not true about XSLT? A. XSLT is a declarative transformation language. B. XSLT uses a set of rules that govern how a document is to be materialized is created. C. XSLT uses a set of procedures that specify how a document is to be programmed. D. XSLT is used to transform the input document into another document. Answer:- C 58. If the XML data instance conforms to the DTD, the document is said to be: A. type-invalid. B. type-valid. C. not-type-valid. D. an HTML document. Answer:- B 59. The document that is used by XSLT to indicate how to transform the elements of the XML document to another format is an A. HTML page. B. DOCTYPE procedure. C. stylesheet. D. stored procedure. Answer:- C 60. If an XML document does not have a DTD, then by definition it is: A. not-type-valid. B. type-valid. C. an HTML document. D. None of the above is correct. Answer:- A 61. Using Which Datasource Control Can Access Oracle DataBase ? a. xmlDatasource b. sqlDataSource c. ObjectDatasource d. All of the Aboove Answer:- C 62. How to get the current user Name ? a. Identity.UserName b. CurrentUser.Identity.Name c. User.Identity.Name d. Based on the Session Variable Name Answer:- C 63.Which is Incorrect ? a. Session objecy is Private to user b. Application Object is global c. Caching can be global or Private d. Application object vaues are accessed accross the application Answer:- C 64. Which is Correct ? a. sql cache invalidation is a new feature in asp.net2.0 b. sql cache invalidation is an existing feature with enhancement c. sql cache invalidation is an existing feature d. sql cache invalidation is a bug in prior version Answer:- D 65.Which is correct ? a. DataTable is member of a DataSet b. DataTable is member of a System.Data c. DataTable is member of a System.Data.SqlClient d. Dataadapter Answer:- B 66.Which is correct ? a. System.String and System.Text are same. System.text is synonym of system.string b. System.String is data type System.Text is Class c. System.String is data type System.Text is namespace d. Non of the abve is right Answer:- D ADO.Net Questions and Answers Pdf Download Read the full article

[wwwtandemlabtech]

SQL Server Compact ADO.NET data access performance – part 1: Overview

In this five part series on SQL Server Compact performance, I will demonstrate and measure the performance of the two available data access APIs provider by the SQL Server Compact ADO.NET provider (System.Data.SqlServerCe.dll). The information in this blog post applies to both version 3.5 and 4.0. I will not cover usage of the unmanaged OLEDB interfaces (using C and C++) As mentioned, the ADO.NET provider provides two APIs for data access (CRUD) operations: One: The T-SQL standards based APIs - SqlCeCommand: ExecuteReader, ExecuteNonQuery, ExecuteScalar, that allows you to use text based SQL statements, including parameterized SQL. Most ORM implementations that support SQL Server Compact, like LINQ to SQL and Entity Framework, use this API, with the exception of the high perfromant OpenNETCF.ORM Framework, which is able to take advantage of Two: The “table direct” APIs that allow you to bypass the T-SQL parsing and interpretation process, and that alone provides an obvious saving in processing. These include: SqlCeResultSet (that inherits from SqlCeDataReader) - in particular when used with a SqlCeCommandType of TableDirect SqlCeUpdateableRecord SqlCeCommand.ExecuteResultSet SqlCeDataReader.Seek SqlCeCommand.SetRange As you can see, none of these is available with the SQL Server ADO.NET objects, and they are particular to SQL Server Compact. This blog post series will demonstrate how to use these as alternatives to the “standard” methods. The diagram from Laxmi’s blog post here explains the advantage graphically: The “table direct” APIs bypass the Query Processor layer, and instead uses an ISAM like access pattern. In the next blog post in this series, I will compare INSERT with SqlCeResultSet.Insert( SqlCeUpdateableRecord ) and demonstrate how to use the “Table Direct” APIs for INSERTs.

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #15 : flush data to disk immediately

Under normal operation, SQL Server Compact keeps all pending disk writes in memory, and flushes them to disk at least every 10 seconds. The connection string property to control this is called “Flush Interval”, and valid values are between 1 and 1000 seconds. But you may want to flush to disk immediately under certain circumstances, and this weeks code snippet demonstrates how to do just that. This is possible via the CommitMode property on the SqlCeTransaction object Commit method, as demonstrated below: using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:dataAdventureWorks.sdf;")) {     conn.Open();     // Start a local transaction; SQL Server Compact supports the following     // isolation levels: ReadCommitted, RepeatableRead, Serializable     using (SqlCeTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))     {         using (SqlCeCommand cmd1 = conn.CreateCommand())         {             // To enlist a command in a transaction, set the Transaction property             cmd1.Transaction = tx;             try             {                 cmd1.CommandText = "INSERT INTO FactSalesQuota " +                     "(EmployeeKey, TimeKey, SalesAmountQuota) " +                     "VALUES (2, 1158, 150000.00)";                 cmd1.ExecuteNonQuery();                 // Commit the changes to disk immediately, if everything above succeeded;                 // Use Deferred mode for optimal performance; the changes will                 // be flashed to disk within the timespan specified in the                 // ConnectionString 'FLUSH INTERVAL' property (default 10 seconds);                 //                 tx.Commit(CommitMode.Immediate);             }             catch (Exception)             {                 tx.Rollback();             }         }     } }  

[cyberponictech-blog]

SQL Server Compact ADO.NET data access performance – part 1: Overview

In this five part series on SQL Server Compact performance, I will demonstrate and measure the performance of the two available data access APIs provider by the SQL Server Compact ADO.NET provider (System.Data.SqlServerCe.dll). The information in this blog post applies to both version 3.5 and 4.0. I will not cover usage of the unmanaged OLEDB interfaces (using C and C++) As mentioned, the ADO.NET provider provides two APIs for data access (CRUD) operations: One: The T-SQL standards based APIs - SqlCeCommand: ExecuteReader, ExecuteNonQuery, ExecuteScalar, that allows you to use text based SQL statements, including parameterized SQL. Most ORM implementations that support SQL Server Compact, like LINQ to SQL and Entity Framework, use this API, with the exception of the high perfromant OpenNETCF.ORM Framework, which is able to take advantage of Two: The “table direct” APIs that allow you to bypass the T-SQL parsing and interpretation process, and that alone provides an obvious saving in processing. These include: SqlCeResultSet (that inherits from SqlCeDataReader) - in particular when used with a SqlCeCommandType of TableDirect SqlCeUpdateableRecord SqlCeCommand.ExecuteResultSet SqlCeDataReader.Seek SqlCeCommand.SetRange As you can see, none of these is available with the SQL Server ADO.NET objects, and they are particular to SQL Server Compact. This blog post series will demonstrate how to use these as alternatives to the “standard” methods. The diagram from Laxmi’s blog post here explains the advantage graphically: The “table direct” APIs bypass the Query Processor layer, and instead uses an ISAM like access pattern. In the next blog post in this series, I will compare INSERT with SqlCeResultSet.Insert( SqlCeUpdateableRecord ) and demonstrate how to use the “Table Direct” APIs for INSERTs.

[loginworksoftware-blog]

HOW DIGITAL TWIN IS TRANSFORMING INTERNET OF THINGS (IOT)?

Every application which deals with database needs to be connected with it and maybe your next question is What, How and in which way?

So we work in two different ways with database connectivity and which deal with dynamic data needs to connect with database and there are many ways to get connected with a database. So in this blog, I am going describe you “How To Work On Connected Disconnected Environment“. I am a .NET developer so I’ll explain the ways of doing this in .NET.

As the number of users accessing application increases to access same data simultaneously which may cause network congestion and this will lead the application to crash because of data transfer between the client application and the data source.

For this kind of situation, .Net introduce ADO in which there are both connected and disconnected environment and both types of connection is a difference just on the basis of connection made to the database. Here’s a diagram of both environment to give guys an overview.

OVERVIEW

The ADO.NET environment categorized into connected and disconnected environments. A connected environment in ADO needs a continuously open connection for transferring data and information between the application and the database until transfer completed or “connection.close()”.

Meanwhile, in disconnected ADO environment transfer data or information and do update database table without a continuously open connection on the network, its only get in touch with a database when needed and also do the opening and closing the connection by itself or automatically.

CONNECTED ENVIRONMENT

In this environment, our code is always in connected phase continuously with a database. Some of the class is fully responsible for connection and action performed on data sources like Connection, Command, DataReader and Transaction.

Whenever a continuous trip to the database on a purpose CRUD (Create, Read, Update and Delete) operation you want to do, it generally creates more traffic to the database but is normally much faster as you should be doing smaller transactions. They include Data Reader/ Command/ Connection Transaction/ Parameter Collection/ Parameter classes.

Few Queries that are used to get data from the database under connected environment:

1. EXECUTEREADER (TO GET A SET OF DATA FROM A TABLE)

using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = new SqlCommand("Select * from TableName", connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine(String.Format("{0}", reader[0]));  } }

2. EXECUTESCALAR (TO GET THE SINGLE VALUE FROM A DATABASE, BELOW QUERY WE ARE GETTING THE AUTOGENERATED ID)

string sql ="INSERT INTO dbo.TableName (Name) VALUES (@Name);"+ "SELECT CAST(scope_identity() AS int)"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add("@Name", SqlDbType.VarChar); cmd.Parameters["@name"].Value = newName; try { conn.Open(); ID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } }

3. EXECUTENONQUERY

using (SqlConnection connection = new SqlConnection(connectionString)) { queryString = "Select * from Employee where Deparment='engineer'"; SqlCommand command = new SqlCommand(queryString, connection); command.Connection.Open(); command.ExecuteNonQuery(); }

DISCONNECTED ENVIRONMENT

In a disconnected environment, we can retrieve data from the data source and manipulate as per need and later reconnect with the data source to update the existing data it’s not a continuously connected with the database.

Some of the classes of ADO disconnected environment is the key or having the main feature which helps us to keep performing an operation on data in client side and update that data in the database whenever it’s needed.

TYPES OF OBJECTS IN ADO.NET DISCONNECTED ARCHITECTURE

The ADO.NET Disconnected architecture considers primarily the following types of objects:

DATASET

A DataSet is on memory store data and it can hold many numbers of tables and also didn’t interact with data source directly.

DataSet ds = new DataSet();

Initialization of the DataSet doesn’t require parameters as the constructor of DataSet don’t have parameters.

SQLDATAADAPTER

It’s the main part of disconnected environment because it manages the connection with the database only when it’s required and close the connection when a task is performed or when it’s not required. SqlDataAdapter also fills the dataset after fetching data from data source.

The SqlDataAdapter holds the SQL commands and connection object to read and write data.

SqlDataAdapter da = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn); da.Fill(ds, "Customers");

The code above creates a new SqlDataAdapter, da and also a SQL statement specifies what data will be read/write into a DataSet. The connection object, conn is already been instantiated, but not opened. It is the SqlDataAdapter’s responsibility to open and close the connection during Fill and Update method calls.

SQLCONNECTION

A connection string which we generally initialized with con is passed in parameter of SqlDataAdapter da in a disconnected environment and SqlCommandBuilder in a connected environment that is passed in parameter of a data provider which connect with the database by the help of it, and a connection string contains server-name, database-name, user-name, password etc.

SqlConnection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");

The code above creates a new SqlConnection conn contains the local as a server name which localhost with windows authentication and database name is Northwind.

SQLCOMMANDBUILDER

A CommandBuilder helps to generate an update, delete and insert commands on a single table for a SqlDataAdapter. Similar to other objects, each data provider has a command builder class.

SqlCommandBuilder cmd = new SqlCommandBuilder(da);

IMPLEMENTATION OF DISCONNECTED ENVIRONMENT

using System; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Windows.Forms; class DisconnectedDataform : Form { private SqlConnection conn; private SqlDataAdapter daCustomers; private DataSet dsCustomers; private DataGrid dgCustomers; private const string tableName = "Customers"; // initialize form with DataGrid and Button public DisconnectedDataform() { // fill dataset Initdata(); // set up datagrid dgCustomers = new DataGrid(); dgCustomers.Location = new Point(5, 5); dgCustomers.Size = new Size(this.ClientRectangle.Size.Width - 10, this.ClientRectangle.Height - 50); dgCustomers.DataSource = dsCustomers; dgCustomers.DataMember = tableName; // create update button Button btnUpdate = new Button(); btnUpdate.Text = "Update"; btnUpdate.Location = new Point(this.ClientRectangle.Width/2 - btnUpdate.Width/2, this.ClientRectangle.Height - (btnUpdate.Height + 10)); btnUpdate.Click += new EventHandler(btnUpdateClicked); // make sure controls appear on form Controls.AddRange(new Control[] { dgCustomers, btnUpdate }); } // set up ADO.NET objects public void Initdata() { // instantiate the connection conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI"); // 1. instantiate a new DataSet dsCustomers = new DataSet(); // 2. init SqlDataAdapter with select command and connection daCustomers = new SqlDataAdapter("select CustomerID, CompanyName from Customers", conn); // 3. fill in insert, update, and delete commands SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers); // 4. fill the dataset daCustomers.Fill(dsCustomers, tableName); } // Update button was clicked public void btnUpdateClicked(object sender, EventArgs e) { // write changes back to DataBase daCustomers.Update(dsCustomers, tableName); } // start the Windows form static void Main() { Application.Run(new DisconnectedDataForm()); } }

It is a wide topic so we can not understand it within a blog. It consists of different approaches that help to achieve the desired task, so we will discuss them later.

I hope this blog will help to get some basic idea of working in .NET both connected and disconnected environment to develop web applications. Feel free to comment your queries in the comments section below.

[crmmanagemet]

Get paid ($$$) : Difference between ExecuteReader, ExecuteNonquery, ExecuteScalar?

http://dlvr.it/PYH5F4

[innercreationthing-blog]

Update Oledb Access columns at run time in C#/.Net

Update Oledb Access columns at run time in C#/.Net

How to add fields to a access table in C# at run time ? This tutorial will share what you need to learn.

.Net Coder

First I have to say that , in this example used the Oledb adapter and connection which is not included in this code. You can check my previous posts on oledb connections(connection and Adapter).

I am going to put a checking section before adding column, others wise C# will…

[crmmanagemet]

Get paid ($$$) : Difference between ExecuteReader, ExecuteNonquery, ExecuteScalar?

http://dlvr.it/NM1Fzq

[crmmanagemet]

Get paid ($$$) : Difference between ExecuteReader, ExecuteNonquery, ExecuteScalar?

http://dlvr.it/NJxcC1